You may need to use a formula for an account in your analysis, but only for a specific scenario you're working with. You can do this by creating scenario-based formulas.
This feature is helpful if you want to Forecast using a formula but still want Actuals data coming from a general ledger. For example:
-
Actuals and Budget scenarios:
Gross Margin = Revenue - COGS
-
Forecast scenario:
Gross Margin = MONTHS_AGO(Revenue, 12) * 1.5 - MONTHS_AGO(COGS, 12) * 1.3
Create a scenario-based formula
The steps to create a scenario-based formula are nearly the same as adding any other formula to Cube, except that you'll select a Scenario as a parent and apply this formula to a specific scenario.
- On your Formulas page, click on the +New button.
- In the window that opens, name the formula and select where this formula dimension should sit in your hierarchy.
- Write your formula using quotation marks around the names of Cube dimensions or select the dimension name from the suggestions. See a list of common formulas and supported operations.
- In the Apply To dropdown menu, select the scenario you'd like this formula to be associated with.
- After you select one scenario, you can select additional scenarios using the +Add scenario override option that appears next to Formula. Use scenario overrides to apply variations of a formula to different scenarios or apply a formula to specific scenarios.
Note: Formulas cannot be applied to the actuals scenario or any scenario with write protection enabled.
- Apply rollup logic and complete any optional fields.
- Click Save Formula to save your changes, and you'll see your formula appear in the list on the Formulas page.
Need further support writing formulas? See this article.