COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: jcrouch on 19 Jul 2012 09:08:40 AM

Title: Rolling 3 Months across 13 months
Post by: jcrouch on 19 Jul 2012 09:08:40 AM
Hi all,

I need a query that will give me rolling 3 months data for each month:

Jan  Feb  Mar  Apr  May  Jun

Jun would include data for Apr,May and Jun. May would have Mar,Apr and May... and so on. This would then be used as the data source for a line chart. The actual data I am using is a little complicated, but think of it as a Count of items for the 3 months.

Ive looked into using the running-total function, but do not see a way to have it restart after every 3 months.

Thanks,

Jeremy



Title: Re: Rolling 3 Months across 13 months
Post by: bi4u2 on 19 Jul 2012 09:39:40 AM
Is the user going to be prompted for the latest month to run the report for? If so, could you create a query item to derive Month Number from the Month in the data? Then you could filter the data to be like Months between (?Latest Month? -2) and ?Latest Month?
Title: Re: Rolling 3 Months across 13 months
Post by: jcrouch on 19 Jul 2012 02:35:20 PM
It will always use the latest months data that is loaded.

But I dont want to filter the data to 3 months, I need at least 13 months. But each month needs to be 3 months of data.