COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: koolbachi on 28 Jul 2010 12:14:58 AM

Title: rolling avergae for 6 months
Post by: koolbachi on 28 Jul 2010 12:14:58 AM
Hi All,

I need to add a rolling 6 month revenue average, where in
its always looking at the last 6 months of revenue from the current month.

if current month is July , it should take an average of the 6 most recent complete months of revenue, which would be Jan,Feb, Mar, April,May,June.

Thanks in advance!!

Gops
Title: Re: rolling avergae for 6 months
Post by: DRL on 30 Jul 2010 04:36:00 AM
To achieve rolling six month window you will first need to create a data item in your query using the date to parse month year combination.

If you don't have a data item with Month year combination, create a new data item in query to parse the date that will give you month year combination. Let's cal lit Month Year

ex: to_char([date], upper('mon - yyyy'))

Using the new data item, Month Year , create two filters in your query to look like this

[Month Year] > add_months ({sysdate}, -7)

[Month Year] <= add_months ({sysdate},-1)


Use this Month year data item in your table/chart display to show six months rolling window

-DRL
Title: Re: rolling avergae for 6 months
Post by: koolbachi on 02 Aug 2010 05:14:28 AM
Hi ,

Thanks for the tip , I will try it and comeback!!


Regards
Gops
Title: Re: rolling avergae for 6 months
Post by: peritas-chris on 05 Aug 2010 11:16:15 AM
Are you using a dimensional or relational source?