Business Case:
User wants to create a chart report that displays rolling 12 months revenue by for current year (In this example 2010). Although 2009 revenues should be used in calculating the rolling totals, the chart should display only 2010 months.
Implementation:
User wants to create a chart report that displays rolling 12 months revenue by for current year (In this example 2010). Although 2009 revenues should be used in calculating the rolling totals, the chart should display only 2010 months.
Implementation:
- Create a base query with data for Prior Year (2009) and and Current Year (2010). My data sample looks like this:
- Set the aggregate property of Revenue in the Base Query to "Total".
- Add a data item called [Dummy] with expression 'Dummy'.
- Create a new query, call it CY and drag the base query as a short cut
- Add the data items from the base query to CY query.
- Add a filter in 'CY' query to filter only for current year's data. In this example, I added:
extract(year, [Order Date]) = 2010
- Create a new query called summary and add a join element.
- Add Base query and CY to the join element. The queries should look like this:
- Join the queries based on the data item [Dummy]. We are basically creating a cartesian product, but since cognos needs a join, we are using a dummy column.
- Now that we have created a cartesian product, we should limit the data to rolling 12 months. So add this filter to the Summary query:
[Base Data].[Order Date] between _add_months([CY].[Order Date],-11) and [CY].[Order Date]
- Add the [Date] from 'CY' query and revenue from "Base Data" Query.
- Create the chart based of the "Summary" query. The report output will look like this:
- If you compare the tabular data and the base data above, you will see that each month's revenue includes that month and the previous 11 months.