Group and Total Issue

[Kid_in_Cognos]

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,

[Lynn]

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.

Code Select Expand


_first_of_month( [ASSGN_DT] )


[Kid_in_Cognos]

Hi Lynn,

Thank you much for the details. I am able to achieve the result now. :)

Regards