Hi ,
I have the data as below.
ASGN_DT CLAIM_AM
Jun 1, 2012 12:00:00 AM 5,000
Jun 4, 2012 12:00:00 AM 728,200
Jun 5, 2012 12:00:00 AM 174
Jun 6, 2012 12:00:00 AM 2,117,151
Jun 7, 2012 12:00:00 AM 32,120.24
Jun 8, 2012 12:00:00 AM 4,166.2
Jun 11, 2012 12:00:00 AM 0
Jun 12, 2012 12:00:00 AM 632,200
I have formatted the date and trying to do the Monthly Total for the CLAIM_AM. I applied group on the ASGN_DT field. But it is not happening.
I am getting the data as below.
ASGN_DT CLAIM_AM
Jun/2012 5,000
Jun/2012 728,200
Jun/2012 174
Jun/2012 2,117,151
Jun/2012 32,120.24
Jun/2012 4,166.2
Jun/2012 0
Jun/2012 632,200
Can you guide me how can I achieve group and total for the particular Month?
Thanks,
If you format the date in your layout then you are simply changing the way the information is displayed. The data is still at the day level of granularity in your result set from the query so all 12 records are still being returned with the date on each masking out the day.
You need to create an expression in your query that squishes out the day detail. This will return one row for each month with the figures aggregated accordingly.
There are lots of different techniques for this. A simple approach would be to assign each date to the first day of the month and then format as you have already done to show just month and year.
_first_of_month( [ASSGN_DT] )
Hi Lynn,
Thank you much for the details. I am able to achieve the result now. :)
Regards