[Solved]How to display the difference between previous year and this year totals

[pravin.cognos]

Dear Gurus

The user selects the reporting period in the prompt page. The difference between the data for this year and previous year, both at row level and summary level needs to be shown in the report. This has to happen for quarter in some reports. I am not sure how to get this. Please help me !!

Thanks
Pravin

[absriram]

Hi Pravin,

What format is reporting period prompt in? Is this a date?

You have to create a filter that has an expression like this: [Date] = ?date_prompt? OR [Date] = _add_months(?date_prompt?,-12)

This will give you data for both current year and previous year. You then have to create two data items one for current year and one for previous year.

E.g. Current Year: Case when [Date] = ?date_prompt? then [Revenue] else 0 end
      Previous Year: Case when [Date] = _add_days(?date_prompt?,-12) then [Revenue] else 0 end

You can then subtract the two columns to get the difference.

Hope this helps.

Sriram.
http://cognosonsteroids.blogspot.com

[LOUM]

Hi Pravin,

The way I achieve this (and I am not sure it is the most efficent) would be to have two identical queries. One has this years totals and the other has last years data.

Then do a UNION query to join them together into one query. You will now have in the joined query current year and previous year data to run your totals on.

I might be over simplifyinig it but it works great for me.

Regards,
Lou

[pravin.cognos]

Hii Absriram & LOUM,

Thanks a lot for your replies. Currently I am on vacation and not able to access Cognos. I will check and update you of the results once I am back to office..

Happy New Year to u both :))

Cheers
Pravin

[pravin.cognos]

Hiii sriram & Lou,

This thing worked in both ways but here we have a lot of columns so I just created one more query with the another filter as Lou said.

Thanks a lot for your help !!

Cheers
Pravin