How to create a rolling 12 month report

Earlier this year at Convergence, we had the opportunity to showcase some of our customer’s reports. During one of the consolidation reports, when we showed the column definition, we had customer’s frantically scribbling down the syntax in the column definition for a rolling 12 months in one column. So I thought I would take the opportunity to show you two ways to do a rolling 12 months column definition. 

There are two ways customers like to use a rolling 12 months report:

1. Each column represents a month and the report dynamically always shows the last 12 months.
2. One columns contains the total of the last 12 months, and each month dynamically updates with the correct data.

 Both methods take advantage of using Base in the Period field in the column definition. The first method is having each column represent a month for the last 12 months. In this example you add a column with the Period syntax of Base -11, a second column with Base-10, a third column with Base-9 and continue this until you have a column of Base. Then add a calculated column with the range of columns you want to add together.

 Figure 1: Column definition for month by month

Figure 2: Rolling 12 month report, month by month

 

The second method allows the user to see the last 12 months in one column. Customers really enjoy doing this especially for side by side reports with divisions, departments or companies. In this example, you add a column with the Period syntax of Base-11:Base. If you want to continue by showing a specific dimension value or company you can add that using the dimension filter or reporting unit restriction column.

Figure 3: Single column definition for rolling 12 months

 

Figure 4: Rolling 12 month report, single column

Comments

• Anonymous
  July 22, 2013
  Hi April I want to have a column similar to figure 3 for budget, i.e. a rolling 12 month budget in one column.  This would therefore have to cover two budgets 2012 and 2013, 2013 and 2014 etc. I am thinking I am going to have to have two columns and add them together.  Your advice on how to do this would be greatly appreciated.

• Anonymous
  July 23, 2013
  Ian, Yes, if it crosses budget IDs you would need two columns, one for each budget ID. You could also attempt to do the conditional printing for each budget and then add them together, such as P<=B for 2013 and p>B for 2014 and then add together. Its something to try to make it as dynamic as possible.

• Anonymous
  November 12, 2013
  Hello April, If you are using 13 periods, will Period 13 appears as its own column? Then, can you exclude Period 13 from the 12-month rolling report? Is there a Conditional Printing to NP any period > 12? If not, can this option be added? Thanks.