RESOLVED - Year Over Year Calculations

[bpothier]

Hello again Cognos wizards,

I am trying to put together a Year Over year Revenue report and have no idea the best way to do this.

I am hoping for a result like this:

                         JAN2011     JAN2012   JANYOY%    FEB2011   FEB2012  FEBYOY% etc
Group name1         
Group name 2

It would be nice if I did not have to change anything going forward - basically so that it always compares current year to previous year

Thank you again for your assistance

Bret

[pricter]

I simply way is to create data items for each month and year
for example for jan 2011

if date =201101 then Revenue
else 0

for jan2012

if date=2012 then Revenue
else 0

and so on

[blom0344]

You would need extract(year,current_date) to make sure you dynamically establish the buckets and then use dynamic list header items (not labels) to make sure that you show the correct month/year combo (otherwise you need to change the list header values each year..)

[bpothier]

that is good news blom0344. Is there a way to extract day and month so it would return Jan-Dec for 2011 and 2012?

[pricter]

It depends from your database
for exapmple in sql you can use MONTH ( date ) and YEAR ( date )

[bpothier]

awesome. I now have it so it shows Jan 2011 Jan 2012 Feb 2011 Feb 2012 etc - which  is what I want

Now I am wondering if I can hook a prompt to it so when the manager selects May for example - it only shows May 2011 May 2012 instead of the whole 2 years

[blom0344]

Sure , simply define a prompt with 12 static values :

1   Jan
2   Feb
..   ....
5   May
.... ....
12 Dec

Then take the associated parameter of the prompt to filter the data query:

extract(month,[somedate]) = ?parameter? and extract(year ,[somedate]) between
extract(year ,[current_date]) - 1 and extract(year ,[current_date])

[bpothier]

thank you so much