Is there a way to create a report to show the rolling twelve month average of an account? I know that I could do this by creating Custom accounts or by attaching a template to a report, but I would like to know if it is possible to do it within the HTML version of the report.
This can be done using a Linked Time Parameter and a Custom Calculation as described below:
- Add all 12 months to the Columns axis and the accounts to the Rows axis of your report.
- Link the 12 months added in Step 1 to one single Time parameter.
- Be sure to drag the last month in the Columns axis to the Parameter area first so that you create the proper connection. This way the report will always show the month chosen for the time parameter plus the previous 11 months. You can check to make sure the last month is set as the Initial Choice by right-clicking on the Time parameter and selecting Properties.
- From the Time parameter Properties you can also set the parameter to always default to a dynamic choice like the current month by selecting the Initial Choice dropdown and scrolling all the way up to the top.
- Add a custom calculation to the Time tier that calculates the average of the 12 months.
- Be sure to use the report elements (on the right) when building the calculation as opposed to static elements (on the left). This ensures that your calculation is dynamic.