Passer au contenu principal

logoCorrectSize.png

Adaptive Insights French

Q: How do you create a year over year growth calculation in a cube sheet?

This is a pretty common requirement and can be easily handled using a Metric account AND an account that will pull prior year values into the current year. 

One restriction with Metric accounts is that they cannot include time references so a Metric account formula that references prior periods will result in 0’s for all consolidation periods (quarters, years, etc.).

Year over year change to Net Revenue example:

Note: This example is using a cube sheet but the steps would be the same for standard sheet accounts.

Step 1: Add a new cube calculated account that pulls in Net Revenue from prior year.  In this example, the account is named PY Net Revenue and the formula is:

ACCT.ProdSalesCube.Net_Revenue[time=this-12]

Step 2: Add a new cube metric account that calculates the change from prior year.  In this example, the account is named YoY % Change and the formula is:

divf(ACCT.ProdSalesCube.Net_Revenue-ACCT.ProdSalesCube.PYNetRevenue,ACCT.ProdSalesCube.PYNetRevenue)

Step 3: Hide the PY Net Revenue account.  You have two options for hiding the account.

Option 1: Sheet Display Options – Select Display Options and under accounts unselect PY Net Revenue.  This change will be user specific unless you set the view as default for all users.

Options 2: Make the account unavailable through the cube sheet editor.  

Tip: The benefit of this method is that is automatically hides the account for all users

The sheet now looks like this.  The values will work correctly for all time, level, and dimension consolidations.

Note: For standard sheets, use a Custom account for the prior year values and add the calculation as a Master Formula.  Use a standard Metric account for the year over year % calculation.