Conditional Formulas Using Dimension Members

You can create conditional calculations in Modeler based on dimensions and their properties.

In the formula bar, you create IF statements that depend on:
  • members of any dimension in the model

    Examples:

    IF ([d/sap.fpa_data:REGION]="DE",[SALES] *2 , [SALES] )
IF ([d/sap.fpa_data:PRODUCT]=("COKE", "SPRITE"),[REVENUE] *1.5, [REVENUE] )
  • properties of a dimension

    Examples:

    IF ([d/sap.fpa_data:VERSION].[p/CATEGORY] = ("ACTUALS") , [Sales] * 2, [Sales] * 3)
IF ([d/sap.fpa_data:VERSION].[p/TYPE] = ("public") , [Sales] * 2, [Sales] * 3)
IF ([d/sap.fpa_data:PRODUCT].[p/Product_TYPE] = ("A" , "B") AND [Region] = ("CA" , "US"),  [Sales] * 2, [Sales] * 3)
Note
The operators = and != allow multiple values. If using multiple values, join them with commas and enclose them in parentheses: ("CA", "US"). Operators >, <, >=, and <= allow only one value. Use AND and OR to join conditions.

For example, consider this scenario:

  • Your model contains a Region dimension.
  • For the US sales region, you want to multiply the sales figures by 2.
  • Otherwise, you want to multiply the sales figures by 3.

You can create a conditional calculation like this:

IF([d/REGION]="US",[SALES]*2,[SALES]*3)

To create this calculation, use the IF function and add the Region dimension to the formula. As you type in the formula bar, dimension names that match the text you have typed are shown. A lower-case “d” indicates a dimension. Instead of typing formulas manually, you can press Ctrl + Space to choose from a list of values that are valid for that location in the formula, or type [ for a list of valid measures and dimensions.

Note

IF formulas have the following capabilities and restrictions:

  • Variables can be included in the <then> and <else> parts of the IF formula.

    Example:

    IF ([d/sap.fpa_data:Time].[h/YM] = "2015",[RevenueLastYear] *['scaling_factor'], [RevenueLastYear])
  • Nested IF statements can be used in the <then> and <else> parts of the IF formula.

    Examples:

    IF ([d/Version].[p/Category]="Actuals", [RevenueLastYear], IF ([d/Version].[p/Category]="Forecast" ,[RevenueLastYear]*1.5, [RevenueLastYear]*1.8))
IF ([d/sap.fpa_data:REGION]="DE", ResultLookup([SALES], d/sap.fpa_data:PRODUCT)="Paper"), [SALES])
  • Multiple operators can be used together.
  • If you use the formula in a chart, the dimensions that you used in the formula must be added to the chart.
  • The formula applies only to the member level where you added the formula; it doesn't apply to parent members.
  • For time hierarchies, if the granularity (for example, YMD) you use in the formula doesn't match the granularity used in the chart, the IF function will evaluate to False.
  • These features are not supported at this time:
    • Location dimensions for Geo models
    • SAP HANA View DATE (SQLDATE) column (dimension)
    • Flat date hierarchies
Related Information