About Script Formulas and Calculations in Advanced Formulas for Planning

Use these predefined formulas, functions, conditions, and operators to write script in an Advanced Formulas step.

This image is interactive. Hover over each area for a description. Click highlighted areas for more information.

Global configuration functions let you set global configuration options that are applied to the entire advanced formulas step. Member selector functions help set the overall scope of your step or return members of a dimension that match certain criteria. Member aggregation functions let you aggregate the members of certain dimensions. These statements let you define variables in your script. A variable is a named data object that refers to a number or virtual dimension member. These keywords let you set conditions on a group of statements so that they only run in specific cases. These functions let you repeat one or more statements iteratively over a specific scope of dimension members, or a specified number of times. These expressions include functions for reading data within the default model or from a different model, and for carrying forward values over multiple time periods. These functions let you write back or delete records where a specified condition is met. Other Basic Syntax Inherited from Account Formulas Time/Period Scope Selection Functions Time Calculation Functions

To learn about the syntax and semantics of advanced formula scripts, check out the SAP Analytics Cloud Advanced Formulas Reference Guide.

Global Configuration Functions

Global configuration functions let you set global configuration options that are applied to the entire advanced formulas step.

CONFIG.GENERATE_UNBOOKED_DATA

This option defines whether the calculation results of the source data to be copied contain unbooked data or not. Unbooked data refers to cells with empty values.

If turned ON, unbooked data (empty value data) in the calculation results will be included to be copied. An unbooked source cell is treated as a value 0 cell. If its corresponding target cell is also unbooked, the target cell will remain unchanged.

If turned OFF, the calculation results to be copied only include booked data. Unbooked data in the calculation results will be ignored and won't impact target values.

Example

You want to copy 2017 December P&L data to 2018 January – use the following formula:

DATA([d/Date]= "201801") = RESULTLOOKUP([d/Date]= "201712")

Before executing the data action, the data is displayed as below:

Set to OFF (the default setting if the configuration is not defined)

The 2017 December data range includes booked data only. If 2017 December source data is empty and the corresponding 2018 January target data has a value, the value will remain unchanged after copying.

Set to ON

The source expression RESULTLOOKUP([d/Date]="201712") returns both booked and unbooked data. When the source expression returns unbooked data in 2017 December, which in this example is "Sales Rebates", "Operating Expense" and so on, the value 0 will be generated in 2018 January for the corresponding cells that have a value.

CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE

If this option is turned on, all calculations consider the sign value (debit or credit) of each account.

Example

To calculate gross margin via Net Revenue (INC) + Cost of Goods Sold (EXP), write the following formula:

DATA([d/ACCOUNT]="Gross Margin") = RESULTLOOKUP([d/ACCOUNT]="NetRevenue") + RESULTLOOKUP([d/ACCOUNT]="CostOfGoodSold")

Set to OFF (the default setting if the configuration is not defined)

Regardless of the ACCOUNT dimension type, RESULTLOOKUP always returns the actual number, like that in a story.

Gross margin (1600) = Net Revenue (900) + Cost of Goods Sold(700)

Set to ON

Gross margin (200) = Net Revenue (900) + Cost of Goods Sold(700)

CONFIG.TIME_HIERARCHY

This configuration governs the time hierarchy of the script.

If this configuration is not defined, the time hierarchy depends on the value of "default Time Hierarchy" in modeling:

  • If the Fiscal Year option is enabled and the default Time Hierarchy is set as "FYQP", advanced formulas will be executed with the same hierarchy as CONFIG.TIME_HIERARCHY = FISCALYEAR
  • If the Fiscal Year option is enabled but the default Time Hierarchy is set as "YQM", advanced formulas will be executed with the same hierarchy as CONFIG.TIME_HIERARCHY = CALENDARYEAR
  • If the Fiscal Year option is disabled, advanced formulas will be executed with the same hierarchy as CONFIG.TIME_HIERARCHY = CALENDARYEAR
  • If both Fiscal Year option and the Week-Based Date Pattern are enabled, advanced formulas will be executed with the same hierarchy as CONFIG.TIME_HIERARCHY = FISCALYEAR

  • If planning Date dimension is set to user managed, advanced formulas can only be executed with the same hierarchy as CONFIG.TIME_HIERARCHY = CALENDARYEAR.

CONFIG.HIERARCHY

This configuration governs the hierarchy of one or more dimensions you will be working on in a script. For any dimension you don’t define a hierarchy here, the default hierarchy of the dimension will be used instead.

Syntax

CONFIG.HIERARCHY [ModelName]=[d/Dimension1].[h/HierarchyName1], [d/Dimension2].[h/HierarchyName2]

  • Currently you can only specify one hierarchy for each dimension.

  • ModelName is optional, if left empty, default model of the advanced formula will be used.

  • If you specify a hierarchy that doesn’t contain all the dimension members, the "Not in Hierarchies" member and its children are excluded from the calculation scope by default. To include these members, use the INCLUDE_MEMBERS_NOT_IN_HIERARCHY syntax:

    • CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY [ModelName] = [d/DimensionName]
  • The hierarchy you set for a dimension in CONFIG will be the hierarchy of the dimension in BASEMEMBER and ELIMMEMBER.

  • If you have defined a hierarchy in CONFIG, you need to set the same hierarchy for the dimension in a data action parameter and not to choose Any or any other hierarchies.

  • This configuration can also be used to govern the hierarchy of a linked model. You can follow the syntax below:
    Sample Code
    MODEL [LinkedModelName]
    	HIERARCHY = [d/Dimension].[h/HierarchyName]
    	…
    ENDMODEL
    Note that when specifying the hierarchy of a dimension in a linked model, you can either use CONFIG.HIERARCHY outside a MODEL statement or HIERARCHY inside one. For example, the following two configurations are equivalent:
    Sample Code
    CONFIG.HIERARCHY [Finance1] =[d/ENTITY].[h/H1]
    Sample Code
    MODEL [Finance1]
    	HIERARCHY = [d/ENTITY].[h/H1]
    	…
    ENDMODEL

CONFIG.TIME_ZONE_OFFSET

This configuration redefines UTC base time with the time zone offset by country for function such as TODAY().

Syntax

CONFIG.TIME_ZONE_OFFSET = [OffsetHour]

  • Unit of the time zone offset is hour. The offset value can be an integer or float ranging from -23 to +23. And if the configuration is not defined, the offset value is 0 by default.

  • [OffsetHour] cannot be a number variable or a number type external parameter.

  • It should be defined before MEMBERSET, IF, FOREACH, DATA and DELETE instructions.

Example

Country

Time Zone

CONFIG.TIME_ZONE_OFFSET

France

UTC+01:00

CONFIG.TIME_ZONE_OFFSET = 1 or +1

Germany

UTC+01:00

CONFIG.TIME_ZONE_OFFSET = 1 or +1

South Korea

UTC+09:00

CONFIG.TIME_ZONE_OFFSET = 9 or +9

United States

UTC-11:00

CONFIG.TIME_ZONE_OFFSET = -11

As shown in the example, if you use the configuration to adjust the standard time to any country’s time zone, later the function TODAY() in your script will return that country’s local system date.

Member Selector Functions

Member selector functions help set the overall scope of your step or return members of a dimension that match certain criteria.

Note
Instead of typing formulas and dimensions 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 dimensions and properties.

If pressing Ctrl + Space doesn't display any value, make sure the hot key is not used elsewhere. For example, in some Chinese input methods, this hot key is used to switch language between Chinese and English. In this case, you need to turn off the input method for the hot key to take effect in the application.

MEMBERSET

Syntax:

MEMBERSET [d/DimensionName] = "DimensionMemberName"

MEMBERSET [d/DimensionName] != "DimensionMemberName"

MEMBERSET statements let you pick which members to include in your advanced formulas step. Used with "!=", you can also pick which members to exclude. Familiarize yourself with the following typical use cases:

  • MEMBERSET [d/ORGANIZATION].[p/REGION] ="ASIA"
  • Set the scope of the Organization dimension's Region to ASIA.
  • MEMBERSET [d/Measures] = ("Amount", "Quantity", "Price")
  • For a model with measures, set the scope of measures to include Amount, Quantity, and Price.
  • MEMBERSET [d/Measures] != ("Amount", "Quantity", "Price")
  • For a model with measures, set the scope of measures to exclude Amount, Quantity, and Price.
  • MEMBERSET [d/FLOW] = ("F_TEMP", "F_NONE")
  • Set the scope of the Flow dimension to the F_TEMP and F_NONE members.
  • MEMBERSET [d/CURRENCY] = [d/ORGANIZATION].[p/CURRENCY]
  • Set the Currency dimension's scope to the value of the Organization dimension's currency attribute.
  • MEMBERSET [d/PRODUCT].[p/FACTOR] = 1
  • Set the scope of the Product dimension's Factor to 1.
  • MEMBERSET [d/PRODUCT].[p/FACTOR] != 100
  • Set the scope of the Product dimension's Factor to exclude 100.
  • MEMBERSET [d/PRODUCT] != BASEMEMBER( [d/PRODUCT].[h/H1], "All_Clothes")
  • Set the scope of the Product dimension to exclude leaf members of All_Clothes.

Substitute of Filters:

For example, say you want to copy data from 2018.January to the 2019.January plan, with a 5% increase.

Consider the following story:

Data before running the data action
Results after running the data action

To achieve this, you can write the scripts below.

Sample Code
MEMBERSET [d/ENTITY] = "US"
MEMBERSET [d/DATE] = "201801"

DATA ([d/Date]="201901") = RESULTLOOKUP () * 1.05

This step only retrieves records for the US and the relevant dates, and then calculates the increase based on those records. Here MEMBERSET statements filter the scope the same way as table filters.

Note

The version filter in the table is not included in the script as version dimension is treated in a special way in advanced formulas and thus can’t be defined in MEMBERSET statements.

You can also filter out data and get the same results within a function (for example, DATA or RESULTLOOKUP), or by using an IF statement. Refer to respective functions for more details.

Use TO as Keyword:

When used with the Date dimension, you can use TO as a keyword.

Example 1:
  • MEMBERSET [d/DATE] ="201701" TO "201712"
  • Set the scope of the Date dimension from 201701 to 201712.
Example 2:
Note

For a system-managed date dimension, to use the "Beginning" and "Ending" attributes of the Version dimension, they should be predefined in the Version dimension with date values as shown below. Then when you execute the data action of the advanced formulas, these data values will become the beginning and ending dates of the time scope.

For a user-managed date dimension, the "Beginning" and "Ending" attributes should be predefined in the Version dimension with the date dimension's member IDs defined by users (indicating leaf members of the hierarchy used in the advanced formulas scripts), as shown below. Then when you execute the data action of the advanced formulas, the time scope will be from the member for "Beginning" to that for "Ending".

  • MEMBERSET[d/Date] = [d/Version].[p/Beginning] to [d/Version].[p/Ending]

If later the end user runs the data action in a private version story, the advanced formulas will get corresponding attribute values from the source public version of the private version, because the version dimension in a private version does not have any attributes.

When used with dimensions with attributes of numeric (integer or decimal) type, you can also use TO as a keyword.

Example 1:
  • MEMBERSET [d/PRODUCT].[p/FACTOR] = 100 TO 300
  • Set the scope of the Product dimension's Factor from 100 to 300.
Example 2:
  • MEMBERSET [d/PRODUCT].[p/FACTOR] = 100 TO %numberParameter%
  • Set the scope of the Product dimension's Factor from 100 to an external parameter of number type.

MEMBERSET with aggregated dimensions:

When the dimension is aggregated using the AGGREGATE_DIMENSIONS statement, you can optionally define a MEMBERSET for the same dimension. The defined dimension members in MEMBERSET are aggregated to the AGGREGATE_WRITETO dimension member. The other members will be excluded from calculations involving the aggregated dimension in the advanced formulas step. Without MEMBERSET, all of the dimension members will automatically aggregate to AGGREGATE_WRITETO dimension member.

For example, you can use MEMBERSET within a MODEL statement to restrict an aggregated dimension from a linked model:
Sample Code
MODEL [BusinessPlanning_Sales]
	AGGREGATE_DIMENSIONS = [d/Channel], [d/StoreType], [d/Product_LineItem]
	MEMBERSET [StoreType] != "Department_Store"
	MEMBERSET [d/Channel] = ("Distribution", "Online", "Direct")
	MEMBERSET [d/Product_LineItem] = BASEMEMBER([d/Product_LineItem].[h/H1], "All_Footwear")
ENDMODEL

DATA() = LINK([BusinessPlanning_Sales], [d/Version] = "Actual", [d/Account] = "Acc_001")

For details, see member aggregation functions.

BASEMEMBER

Set the scope to all base members of one or more specific parent members. Base members refer to the leaf members without any child members.

Syntax:

BASEMEMBER ([DimensionName],[ParentMemberName])

BASEMEMBER ([DimensionName],[ParentMemberName1], [ParentMemberName2]…)

Refer to the following examples for more detailed use cases.

Typical Example:

  • CONFIG.HIERARCHY = [d/ENTITY].[h/H1]
  • MEMBERSET [d/ENTITY] = BASEMEMBER([d/ENTITY], "World")
  • Return all base members of the member "World" in hierarchy H1 and set these base members as the member scope of the ENTITY dimension.
  • If you haven’t defined hierarchy for the dimension in CONFIG.HIERARCHY, BASEMEMBER will use the default hierarchy of the dimension.
  • CONFIG.HIERARCHY = [d/ENTITY].[h/H1]
  • MEMBERSET [d/ENTITY] = BASEMEMBER([d/ENTITY], "US", "World")
  • Return all base members of the member "US" and the member "World" in hierarchy H1 and set these base members as the member scope of the ENTITY dimension.
  • You can also use one or more external parameters to set the member scope. For example:
  • CONFIG.HIERARCHY = [d/ENTITY].[h/H1]
  • MEMBERSET [d/ENTITY] = BASEMEMBER([d/ENTITY], %Region1%, %Region2%)
  • You can also use a mixture of parent members and external parameters to set the member scope. For example:
  • CONFIG.HIERARCHY = [d/ENTITY].[h/H1]
  • MEMBERSET [d/ENTITY] = BASEMEMBER([d/ENTITY], "ASIA", "AFRICA", %Region1%, %Region2%)

When used with a date dimension, the member should be defined with an argument specified from the year granularity level to the finest granularity level required to identify the member.

Sample Code
//The granularity here is year.

MEMBERSET [d/Time] = BASEMEMBER([d/Time], "[2017]")
IF [d/Time] = BASEMEMBER([d/Time], "[2017]") THEN
Sample Code
//The granularity here is quarter.

MEMBERSET [d/Time] = BASEMEMBER([d/Time], "[2017].[20173]")
IF [d/Time] = BASEMEMBER([d/Time], "[2017].[20173]") THEN
Sample Code
//The granularity here is month.

MEMBERSET [d/Time] = BASEMEMBER([d/Time], "[2017].[20173].[201709]")
IF [d/Time] = BASEMEMBER([d/Time], "[2017].[20173].[201709]") THEN
BASEMEMBER can also be used inside MODEL…ENDMODEL to help define the linked model scope. For example:
Sample Code
MODEL [HR]
	MEMBERSET [d/Account_HR] = BASEMEMBER([d/Account_HR].[h/parentId], "Salary_Month_Total")
ENDMODEL

ELIMMEMBER

Business Meanings:

ELIMMEMBER returns the names of the elimination members below the first common parent of the two companies that did inter-company trading. For example:

In the case above, the inter-company transaction data between Korea and China will be eliminated after copying to Elimination Asia, which is the elimination member under their common parent, Asia.

For the inter-company transaction data between Korea and France, the value will be copied to Elimination World under their first common parent, World.

Using ElimMember(), you can directly get Elimination Asia as the elimination member of Korea and China and Elimination World as the elimination member of Korea and France.

Syntax:

ELIMMEMBER ([DimensionName], [MemberName1], [MemberName2], [DimensionAttribute])

Note
  • The attribute defined in DimensionAttribute should be maintained with its value in the Organization dimension beforehand.
  • DimensionAttribute is optional. Other parameters are mandatory. If DimensionAttribute is omitted, the default value [d/ElimDimensionName].[p/ELIMINATION]= "Y" is assumed. In this case, the ELIMINATION attribute of corresponding elimination dimensions in Organization dimension should be defined with "Y":

    :

  • MemberName1 and MemberName2 should be in the same Organization dimension of the DimensionHierarchyName.
  • MemberName1 and MemberName2 can be defined not only as specific members, but also as a member set in the format like [d/ORG] or [d/INTCO].[p/ORG]. In this case, the member set is composed of all members of the Organization dimension, or all members of the INTCO dimension, with Organization as an attribute.
  • ELIMMEMBER is currently only allowed in DATA ().
  • External parameters aren't supported for the member names, for example ELIMMEMBER([d/ORG], %Org1%,%Org2%).

Typical Example:

To familiarize yourself with the function, refer to the following example for more details.

  • CONFIG.HIERARCHY= [d/ORG].[h/H1]
  • Data ([d/ORG]=ELIMMEMBER([d/ORG], [d/ORG],[d/INTCO].[p/ORG],[d/ORG].[p/ELIMINATION]= "Y"))=RESULTLOOKUP () *-1

ELIMMEMBER returns the elimination dimension member whose ELIMINATION attribute is "Y" under the first common parent member of the Organization dimension members and their INTCO dimension members in hierarchy H1. Then, the value RESULTLOOKUP() *-1 is generated and written to the elimination dimension member.

If you haven’t defined hierarchy for the dimension in CONFIG.HIERARCHY, ELIMMEMBER will use the default hierarchy of the dimension.

Member Aggregation Functions

Member aggregation functions let you aggregate the members of certain dimensions.

In advanced formulas, dimensions outside the calculation scope are not aggregated automatically by themselves before performing the calculations. If you want to aggregate members of one or more dimensions, you can use these functions.

Aggregated dimensions don’t create additional records when they are included in the calculation scope. For example, if you run a calculation that includes a date dimension and an aggregated region dimension, the calculation runs on each date period using the aggregated value from all regions. With a non-aggregated region dimension, the calculation would run on each combination of date and region leaf members.

For details, see Four Things You Need to Know Before Getting Started.

This behavior is similar to filtering a dimension to a single leaf member. For example, the following scripts give the same results:
Sample Code
Without aggregation
MEMBERSET [d/Product] = ("P1", "P2", "P3")

DATA([d/Product] = "#") = RESULTLOOKUP([d/Date] = PREVIOUS(12))
Sample Code
With aggregation
MEMBERSET [d/Product] = ("P1", "P2", "P3")
AGGREGATE_DIMENSIONS = [d/Product]

AGGREGATE_WRITETO [d/Product] = "#"

DATA() = RESULTLOOKUP([d/Date] = PREVIOUS(12))

AGGREGATE_DIMENSIONS

Use this function to define the list of dimensions from the default model that will be aggregated before performing any calculations.

Syntax:

AGGREGATE_DIMENSIONS[d/DimensionName]

Example:

AGGREGATE_DIMENSIONS [d/CostCenter], [d/ProfitCenter]

Remarks:
  • By default, all members of each dimension you define in this function are aggregated.

  • If you want to further restrict the aggregation scope, use the MEMBERSET keyword to define the dimension member scope like in other common cases.

  • You can’t aggregate versions or measures.

  • You can’t use aggregated dimensions in DATA, RESULTLOOKUP, DELETE, ATTRIBUTE, CARRYFORWARD, or LINK functions. They’ll cause validation errors in these cases.

  • If you aggregate a planning date dimension, you won’t be able to use it in a CARRYFORWARD function.

  • To aggregate dimensions of a linked model, use the AGGREGATE_DIMENSIONS function inside MODEL … ENDMODEL

For each aggregated dimension from the default model, you’ll also need to specify a leaf member using AGGREGATE_WRITETO.

AGGREGATE_WRITETO

Use this function to define a leaf member for each aggregated dimension. This leaf member will receive any values that are written to the dimension using the DATA function. In effect, it works like the Aggregate To option for copy steps and cross-model copy steps in a data action.

Syntax:

AGGREGATE_WRITETO [d/Dimension]="DimensionMemberName"

Examples:

AGGREGATE_WRITETO [d/CostCenter] = "#"

AGGREGATE_WRITETO

To aggregate dimensions of a linked model, define AGGREGATE_DIMENSIONS functions inside a MODEL statement.

You can also use MEMBERSET functions within a MODEL statement to filter the aggregated dimensions. No other functions are allowed.

The AGGREGATE_WRITETO definition isn’t required for these dimensions, because you can’t write data to the linked model.

Syntax:

Sample Code
MODEL [ModelName]…
  {statement}
ENDMODEL

Examples:

MODEL [Capex]
	AGGREGATE_DIMENSIONS [d/CostCenter], [d/ProfitCenter]
	MEMBERSET [d/CostCenter] = ("C1", "C2")
ENDMODEL

DATA()=LINK([Capex], [d/Version]="Actual",[d/Account]="Acc_001",[d/Flow]="Closing") 
Note

In this example, [d/CostCenter] and [d/ProfitCenter] can't be defined within the LINK formula, because they are set as aggregated dimensions.

Time/Period Scope Selection Functions

For the time/period scope selection functions, their date type argument can be one of the following:

  • A date type dimension

  • A dimension attribute of date format

  • TODAY()

  • A single selection external parameter of a Date dimension

  • A fixed date

    Allowed date formats: YYYY-MM-DD, YYYY-MM, YYYY-Q, YYYY, YYYYMMDD, YYYYMM, YYYYQ, YYYY/MM/DD, YYYY/MM, YYYY/Q, YYYY.MM.DD, YYYY.MM, YYYY.Q

When the date type argument is not specified, the default value is the time dimension for planning you specified in the Model Preferences.

Note

When writing a time/period selection function, you can’t define a calendar date dimension member for a fiscal date dimension, and vice versa. For example, this case is invalid: DATA([d/Calendar_Date]) = RESULTLOOKUP(NEXT(2, "YEAR", [d/Fiscal_Date]))

Note

You can’t define any time/period selection function within a time calculation function (such as DATERATIO, DAYSINMONTH, DAY). For example, this case is invalid: DAY(LAST()).

Functions

Syntax

Typical Examples

Remarks

NEXT

Returns the time period or a date that adds a specified offset value (a signed integer) of a certain granularity to the current date.

NEXT (offset)

NEXT (offset,granularity)

NEXT (offset,granularity,[date])

MEMBERSET [d/Date2] = "201701" TO "201712"

DATA() = RESULTLOOKUP([d/Date1] = NEXT(2, "YEAR", [d/Date2]))

Here, RESULTLOOKUP returns the value when the dimension Date1 member ranges from 201901 to 201912 (two years after the time period of Date2).

MEMBERSET [d/Date] = [d/Version].[p/Beginning] TO NEXT(12, "Month", TODAY())

This example defines a member set from the beginning date to the 12th month starting from the current system date.

  • Only define this function for a date type dimension.

  • The offset argument is optional. The default value is 1.

  • The offset value can be a number, a number variable or a number type external parameter.

  • The granularity argument is optional. If you define it, you must also define an offset. The default value is the granularity of the time dimension that you’re applying the function to. For example, the function RESULTLOOKUP([d/Date1] = NEXT(2) uses the granularity of the Date1 dimension.

  • The granularity value can be Day, Week, Month, Quarter or Year.

  • The [date] argument is optional. To define it, you must also define an offset and granularity. The default value is the time dimension for planning you specified in the Model Preferences.

  • After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
    Sample Code
    MODEL [BOM]
    	…
    ENDMODEL
    
    DATA([d/Date] = NEXT(1, "MONTH", [BOM].[d/Date])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")

PREVIOUS

Returns the time period or a date that deducts a specified offset value (a signed integer) of a certain granularity from the current date dimension, a dimension attribute of date format, or a fixed date.

PREVIOUS (offset)

PREVIOUS(offset,granularity)

PREVIOUS(offset,granularity,[date])

MEMBERSET [d/Date2] = "202001" TO "202012"

DATA() = RESULTLOOKUP([d/Date1] = PREVIOUS(12, "MONTH", [d/Date2]))

Here, RESULTLOOKUP returns the value when the dimension Date1 member ranges from 201901 to 201912 (12 months before the time period of Date2).

MEMBERSET [d/Date] = PREVIOUS(3, "MONTH", %StartMonth%) TO NEXT(3, "MONTH", %StartMonth%)

This example defines a member set from 3 months before the start month until 3 months after the start month.

  • Only define this function for a date type dimension.

  • The offset argument is optional. The default value is 1.

  • The offset value can be a number, a number variable or a number type external parameter.

  • The granularity argument is optional. If you define it, you must also define an offset. The default value is the granularity of the time dimension that you’re applying the function to. For example, the function RESULTLOOKUP([d/Date1] = PREVIOUS(2) uses the granularity of the Date1 dimension.

  • The granularity value can be Day, Week, Month, Quarter or Year.

  • The [date] argument is optional. To define it, you must also define an offset and granularity. The default value is the time dimension for planning you specified in Model Preferences.

  • After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
    Sample Code
    MODEL [BOM]
    	…
    ENDMODEL
    
    DATA([d/Date] = PREVIOUS(1, "MONTH", [BOM].[d/Date])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")

TODAY

Returns current system’s date in Coordinated Universal Time (UTC) in the format YYYYMMDD.

Note

Always returns system time based on UTC 0 by default. To adjust it to the user’s time zone, configure another function CONFIG.TIME_ZONE_OFFSET.

TODAY()

MEMBERSET [d/Time]= PERIOD(TODAY()) TO "20201231"

The example defines the forecast time scope from current date till end of year 2020.

As demonstrated in the typical example, TODAY() can be defined in all time calculation functions, for example:

PERIOD(TODAY())

Also the value TODAY() returns is a string, not a time dimension member or numeric number, so don’t directly define it as a member in MEMBERSET, FOREACH, IF, Calculation Expressions and ATTRIBUTE or as a number variable. For example, the case below is invalid:

DATA() = RESULTLOOKUP([d/Time] = TODAY())

Instead, you need to write the script like:

DATA() = RESULTLOOKUP([d/Time] = PERIOD(TODAY()))

Besides this, it’s a function that returns an actual date, so don’t redefine a new date for it. If you write like TODAY() = "20190506", the script will be invalid.

FIRST

Returns the first period of the year taken from the given date argument.

FIRST([date])

CONFIG.TIME_HIERARCHY=FISCALYEAR

MEMBERSET [d/Employee]=("E001","E002")

DATA([d/Date1]=FIRST([d/Employee].[p/Startdate])) =RESULTLOOKUP()

Writes data to the first period of employee E001 and E002’s start dates. If their start dates are both "2020.02.01", this script first translates it to date member "2020.02", then returns the first member of the fiscal year 2020 as "2020.01".

MEMBERSET [d/Date]= "202001" to "202012"

DATA()=RESULTLOOKUP([d/Date] = FIRST())

The date ranges from 2020 Jan. to 2020 Dec. For each period, FIRST() returns "202001".

  • The value returned is a member of Date dimension.

  • If the date argument is a dimension attribute or fixed date, no matter the fiscal year settings is turned on or not, the function will directly take the exact year from the date.

  • You can’t define FIRST() for MEMBERSET. However, other time scope selection functions that return a time range are unrestricted, for example NEXT() and PREVIOUS().

  • If you define FIRST() in the LINK function, you can only define it for a planning date dimension instead of a generic time dimension.

  • After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
    Sample Code
    MODEL [BOM]
    	…
    ENDMODEL
    
    DATA([d/Date] = FIRST([BOM].[d/Date])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")

LAST

Returns the last time period of the year taken from the given date argument.

LAST ([date])

CONFIG.TIME_HIERARCHY=FISCALYEAR

MEMBERSET [d/Employee]=("E001","E002")

DATA()=RESULTLOOKUP([d/Date1]=LAST([d/Employee].[p/Startdate]))

Returns the last period of employee E001 and E002’s start dates. If their start dates are both "2020.02.01", this script first translates it to date member "2020.02", then returns the last member of the fiscal year 2020 as "2020.12".

MEMBERSET [d/Date]= "202001" to "202012"

DATA()=RESULTLOOKUP([d/Date] = LAST())

The date ranges from 2020 Jan. to 2020 Dec. In this case, writes data of the last period of current year, which is "202012" to the target. For each period, LAST() returns "202012".

  • The value returned is a member of Date dimension.

  • If the date argument is a dimension attribute or fixed date, no matter the fiscal year settings is turned on or not, the function will directly take the exact year from the date.

  • You can’t define LAST() for MEMBERSET. However, other time scope selection functions that return a time range are unrestricted, for example NEXT() and PREVIOUS().

  • If you define LAST() in the LINK function, you can only define it for a planning date dimension instead of a generic time dimension.

  • After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
    Sample Code
    MODEL [BOM]
    	…
    ENDMODEL
    
    DATA([d/Date] = LAST([BOM].[d/Date])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")

PREYEARLAST

Returns the last period of the year prior to the year taken from the given date argument.

PREYEARLAST ([date])

CONFIG.TIME_HIERARCHY=FISCALYEAR

MEMBERSET [d/Employee]=("E001","E002")

DATA()=RESULTLOOKUP([d/Date1]=PREYEARLAST([d/Employee].[p/Startdate]))

Returns the last period of the previous year of employee E001 and E002’s start dates. If their start dates are both "2020.02.01", this script first translates it to date member "2020.02", then returns the last member of the previous fiscal year 2019 as "2019.12".

MEMBERSET [d/Date]= "202001" to "202012"

DATA()=RESULTLOOKUP([d/Date] = PREYEARLAST())

The date ranges from 2020 Jan. to 2020 Dec. For each period, PREYEARLAST() returns "201912".

  • The value returned is Date dimension member.

  • If the date argument is a dimension attribute or fixed date, no matter the fiscal year settings is turned on or not, the function will directly take the exact year from the date.

  • You can’t define PREYEARLAST() for MEMBERSET. However, other time scope selection functions that return a time range are unrestricted, for example NEXT() and PREVIOUS().

  • If you define PREYEARLAST() in the LINK function, you can only define it for a planning date dimension instead of a generic time dimension.

  • After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
    Sample Code
    MODEL [BOM]
    	…
    ENDMODEL
    
    DATA([d/Date] = PREYEARLAST([BOM].[d/Date])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")

Time Calculation Functions

There are some rules and restrictions that apply to all the time calculation functions.

Valid date formats

For all the dates defined in these functions, no matter they are directly written as fixed dates or are taken from the attribute values of a certain dimension, the valid date formats should be one of the following: YYYY-MM-DD, YYYY-MM, YYYY-Q, YYYY, YYYYMMDD, YYYYMM, YYYYQ, YYYY/MM/DD, YYYY/MM, YYYY/Q, YYYY.MM.DD, YYYY.MM, YYYY.Q

Note

When the time granularity of a date is larger than the time information you want to capture or calculate, for example, if you want to capture the day of a given date "2019-01", then the function will return a default value 1, the first day of January. Or if you want to capture the month of a given date "2019-2", then the function will return a default value 4, the first month of the second quarter.

Fiscal year settings

When the dates you define in these functions are in the format of time dimension and fiscal year is defined in CONFIG.TIME_HIERARCHY, the functions will first translate fiscal year or fiscal month in the time dimension to a calendar year and month before performing any calculations.

When the dates you define in these functions are directly written as fixed dates or are taken from the attribute values of a certain dimension, no matter the fiscal year setting is turned on or not, these functions will directly take the exact month or year in the dates.

Valid and invalid cases in an IF condition

When using the logical operators (AND, OR) in an IF condition, only the case below is supported:

Sample Code
IF Dimension filter AND Dimension filter THEN // Supported.

This also applies to the time calculation functions, for example:

Sample Code
IF [d/Time]=PERIOD("201908") AND [d/Entity]="US" THEN // Supported. 
IF RESULTLOOKUP([d/Time]="201908")>10 AND RESULTLOOKUP()<DAY("201908") THEN // Not supported as cell value filters are defined.

In addition to this, you cannot define two identical time calculation functions as different conditions of an AND operator in an IF condition, for example:

Sample Code
IF [d/Time] = PERIOD("20190826") AND [d/Time2] = PERIOD("20190826") THEN // This case is not supported. 
IF [d/Time]=PERIOD("201908") AND [d/Entity]="US" THEN // this case is supported

Also you cannot nest a time calculation function inside another, for example like: DAY(PERIOD("201901")) and cannot compare time calculation functions in an IF condition, such as IF DAY() > YEAR() THEN.

Different cases when used with other functions

All the time calculation functions are supported in calculation expressions and IF conditions and can be used with external parameter, for example:

Sample Code
MONTH(%TimeMember%)

DATEDIFF (%shipDate%, %OrderDate%, "DAY")

When used with an external parameter, only parameters of date dimension are supported. The cardinality of the external parameter must be single. For a DATERATIO function, in addition to that, when it's used with an external parameter, only the third parameter [timePeriod] in this time function can be defined as parameter:

Sample Code
DATERATIO("2019-04-21", "2019-08-31", %DateMember%)

Except for PERIOD, all the other time calculation functions are supported in mathematical functions.

Restrictions when defined as a number variable

All time calculation functions except PERIOD() can be defined as number variables.

When the functions are defined as number variables, they must return single value. For example you can define a fixed date or an external parameter of time dimension for a time function:

Sample Code
Case1: @Num = DAY("2019-03-15") 
Case2: @Num = DAY(%TimeMember%) 
Case3: @ Ratio = DATERATIO("2019-04-21", "2019-08-31", "201904")

But if you define a dimension or dimension attribute for a time function, it’s not supported in this case because multiple values can be returned.

However, if you multiple such functions by RESULTLOOKUP function, then a single value can be returned which allows the result to be defined as number variables. For example, the following cases are valid:

Sample Code
Case 4: @Num = DAY([d/Time])*RESULTLOOKUP()
Case 5: @Num = DAY([d/Entity].[p/StartDate]) *RESULTLOOKUP()

DAY, WEEK, MONTH, YEAR, PERIOD

Function

Description

Syntax

Example & Remarks

DAY

Returns the day in a date. The day will be in number format such as 31.

Three options are available:

DAY("fixedDate")

DAY([d/TimeDimensionName])

DAY([d/dimensionName].[p/AttributeName])

Note

fixedDate refers to a specific date such as 2019-03-15.

RESULTLOOKUP([d/ACCOUNT]="AnnualSalary", [d/Time]= "201903") *

(DAY("2019-03-15") / DAYSINMONTH("2019-03-15"))

Remarks:

It returns March's monthly salary till the date March. 15th.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA() = RESULTLOOKUP() * DAY([BOM].[d/Date])

WEEK

Returns the week in a date. The week will be in number format such as 1.

Three options are available:

WEEK("fixedDate","calculationMode")

WEEK([d/TimeDimensionName])

WEEK([d/DimensionName].[p/AttributeName])

Note

fixedDate refers to a specific date such as 2019-03-15.

Note

The calculationMode argument is optional and is set to CALENDAR by default. It dictates how to return the week number based on either ISO (calendar system issued by International Organization for Standardization) or CALENDAR (Gregorian calendar used in most of the world) mode. However, it is not available when the WEEK function is defined with a date type dimension or a member parameter of date type dimension.

WEEK("2021-08-15","ISO")

WEEK([d/Employee].[p/StartDate])

Remarks:

First example returns the week number in the date as a number, which is 32. Second example returns the week number taken from the start date of an employee.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA() = RESULTLOOKUP() * WEEK([BOM].[d/Date])

MONTH

Returns the month in a date. The month will be in number format such as 12.

Three options are available:

MONTH("fixedDate")

MONTH([d/TimeDimensionName])

MONTH([d/DimensionName].[p/AttributeName])

Note

fixedDate refers to a specific date such as 2019-03-15.

MONTH("2019-03-15")

MONTH([d/Employee].[p/StartDate])

Remarks:

First example returns month in the date as number, which is 3. Second example returns the month number taken from the start date of an employee.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA() = RESULTLOOKUP() * MONTH([BOM].[d/Date])

YEAR

Returns the year in a date. The year will be in number format such as 2019.

Three options are available:

YEAR("fixedDate")

YEAR([d/TimeDimensionName])

YEAR([d/DimensionName].[p/AttributeName])

Note

fixedDate refers to a specific date such as 2019-03-15.

YEAR("2019-03-15")

YEAR([d/Employee].[p/StartDate])

Remarks:

First example returns year in the date as number, which is 2019. Second example returns the year number taken from the start date of an employee.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA() = RESULTLOOKUP() + YEAR([BOM].[d/Date])

Period

Converts a date to a time period based on the time granularity.

Three options are available:

PERIOD("fixedDate")

PERIOD([d/TimeDimensionName])

PERIOD([d/DimensionName].[p/AttributeName])

Note

fixedDate refers to a specific date such as 2019-03-15.

PERIOD("2019-03-15")

PERIOD([d/Employee].[p/StartDate])

Remarks:

First example returns period in the date as member of date dimension. If the granularity of time dimension is month, it will return 201903. Second example returns the period taken from the start date of an employee.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA([d/Date] = PERIOD([BOM].[d/Date])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")

DAYSINMONTH, DAYSINYEAR

Function

Description

Syntax

Example & Remarks

DAYSINMONTH

Number of days in a calendar month. The month can be taken from a specific date, time dimension or dimension attribute.

Three options are available:

DAYSINMONTH("fixedDate")

DAYSINMONTH([d/TimeDimensionName])

DAYSINMONTH([d/DimensionName].[p/AttributeName])

DAYSINMONTH("2019-03-15")

Remarks:

The example returns number of days in March, which is 31.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA() = RESULTLOOKUP() * DAYSINMONTH([BOM].[d/Date])

DAYSINYEAR

Number of days in a calendar year. The year is a 4-digit number taken from a specific date, time dimension or dimension attribute.

Three options are available:

DAYSINYEAR("fixedDate")

DAYSINYEAR([d/TimeDimensionName])

DAYSINYEAR ([d/DimensionName].[p/AttributeName])

RESULTLOOKUP([d/ACCOUNT]="AnnualSalary") * DAYSINMONTH("2019-03-15") / (DAYSINYEAR("2019-03-15"))

Remarks:

The example estimates 2019 March’s monthly salary by average time.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [BOM]
	…
ENDMODEL

DATA() = RESULTLOOKUP() + DAYSINYEAR([BOM].[d/Date])

DATERATIO, DATEDIFF

Function

Description

Syntax

Example & Remarks

DATERATIO

Calculates how many days between the start and end date overlaps the given period. Then divides the overlapping days by the total number of days in the period.

Two options are available:

DATERATIO("StartDate", "EndDate", [TimePeriod])

DATERATIO([d/DimensionName].[p/AttributeName1], [d/DimensionName].[p/AttributeName2], [TimePeriod])

Note
  • In the first syntax, StartDate and EndDate are in the format of a specific date such as 2019-03-15.

  • In the second syntax, dimension should not be a date dimension.

  • timePeriod is optional, if not specified or if there are more than 1 date dimension in the model, will take the time dimension for planning you specified in Model Preferences.

  • timePeriod must be a date dimension, date dimension attribute, external parameter or a fixed date.

  • When used with an external parameter, only the third parameter timePeriod in this time function can be defined as parameter. And only parameters of date dimension with single cardinality are supported.

DATERATIO("2019-01-16", "2019-02-07", "201902")

Remarks:

In this example, the overlapping period is from 2019-02-01 to 2019-02-07, in total 7 days. February has 28 days in total. The final result will be 7 days / 28 days = 0.25.

After the linked model is defined, dimensions from the linked model can also be used in this function. For example:
Sample Code
MODEL [HR]
	…
ENDMODEL

DATA() = RESULTLOOKUP() * DATERATIO([HR].[d/Employee].[p/ StartDate], [HR].[d/Employee].[p/EndDate], TODAY())

DATEDIFF

Returns the period of time between two dates of a specified time granularity. The granularity can be day, month, quarter or year.

Three options are available:

DATEDIFF(fixedDate1,fixedDate2,granularity, calculationMode)

DATEDIFF([d/TimeDimensionName1],[d/TimeDimensionName2],granularity, calculationMode)

DATEDIFF([d/DimensionName].[p/PropertyName1],[d/DimensionName].[p/PropertyName2],granularity, calculationMode)

Note
  • Date difference = Date2-Date1

    If Date1 is later than Date2, a negative number can be returned, for example:

    DATEDIFF("2019-09","2019-06","MONTH") returns -3.

  • Value of the granularity can be Day, Week, Month, Quarter or Year.

  • In the first syntax, fixedDate1 and fixedDate2 can be a specific date such as 2019-03-15 or can be external parameters. When they’re external parameters, only parameters of date dimension with single cardinality are supported.

  • In the second syntax, the dimensions must be date dimensions.

  • The calculation mode is optional. It determines how to handle the remainder when dates have a finer granularity than the function.

    • CalendarDiff (default): Only the specified granularities of the dates are compared. If you specified year granularity, for example, January 10, 2020 is treated as 2020.

    • Floor: The result is rounded down to the lower absolute value.

    • Ceiling: The result is rounded up to the higher absolute value.

  • When it returns a single value, it’s supported in calculation expressions, mathematical functions and IF conditions, and can be defined as a number variable.

  • When it returns multiple values, it’s not supported in calculation expressions, for example, this case below is NOT valid:

    DATA() = DATEDIFF("2019-04-21", [d/Employee].[p/EndDate], "MONTH")

Used in an IF condition:

IF DATEDIFF([d/Employee].[p/StartingDate], [d/Employee].[p/EndingDate], "MONTH") > 12 THEN

Used in calculation expression:

DATA()= DATEDIFF([d/Employee].[p/OnboardingDate], %EndDate%, "MONTH") * RESULTLOOKUP()

Defined as number variables:

@DiffMonth = DATEDIFF(%EndDate%, TODAY(), "MONTH")

Defined with different granularities and calculation methods:

DATEDIFF("2019-06-01", "2019-09-13","MONTH", "Floor") returns 3. ( 3 months 12 days, rounded to the lower absolute value )

DATEDIFF("2019-06-01", "2019-09-13","DAY") returns 104 =(30+31+31+13)-1=104

DATEDIFF("2019-09-13","2020-11-01","YEAR", "CalendarDiff") returns 1 ( first date is calculated as 2019, second date is calculated as 2020 )

DATEDIFF("2019-09-13", "2019-06-01","QUARTER", "Floor") returns -1. ( -1 quarter 12 days, rounded to the lower absolute value )

DATEDIFF("2019-09-13", "2019-06-01","QUARTER", "Ceiling") returns -2. ( -1 quarter 12 days, rounded to the higher absolute value )

After the linked model is defined, dimensions from the linked model can also be used in this function:
Sample Code
MODEL [BOM]
	…
ENDMODEL

IF DATEDIFF([d/Date], [BOM].[d/ValidTo], "MONTH") >= 0 THEN
	IF DATEDIFF([d/Date], [BOM].[d/Date], "MONTH") <= 0 THEN
		DATA([d/ProductGroup_P] = "SKU") = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")
	ENDIF
ENDIF

Variable Definition

These statements let you define variables in your script. A variable is a named data object that refers to a number or virtual dimension member.

Virtual Variable Member

You can define this virtual member to store intermediate calculated values that can be reused elsewhere, whenever necessary, to avoid calculating the value repeatedly.

The virtual variable member will be stored independently and cannot be added to a dimension like a real dimension member. After defining a variable member for a certain dimension, you can use the variable member to store values only for that specific dimension.

Syntax

VARIABLEMEMBER #VariableMemberName OF [d/DimensionName]

Note
  • Write this definition after the configuration settings and before all the instructions.
  • Create a new name for a virtual variable member. Make sure, in the variable member definition, it always starts with the prefix "#".
  • DimensionName is mandatory and must be an existing dimension in the current data action model. The version dimension is not supported, although measures are, for example: VARIABLEMEMBER #Total_Revenue OF [d/Measures]
  • Currently, a virtual variable member can only be used in DATA and RESULTLOOKUP functions. When inputting "#" in DATA() or RESULTLOOKUP(), a list of all available virtual members will be shown.

Length of the Variable:

Variable member length is in accordance with its dimension length. For example:

  • If the dimension is an account dimension created in SAP Analytics Cloud, the available variable member length should be 256.

  • If the dimension is an entity dimension imported into SAP Analytics Cloud from BPC, the available variable member length should be 32.

  • If the dimension is a date dimension, the length depends on its Lowest Granularity:

    • If Lowest Granularity is set as Month, the available variable member length should be 6.

    • If Lowest Granularity is set as Day, the available variable member length should be 8.

Typical Examples:

This example is to return a sales rebate of 10% when the total sales amount of 2018 January exceeds 1000.

Sample Code
MEMBERSET [d/Date] = "201801"
MEMBERSET [d/Product] = ("PRD0001","PRD0002","PRD0003")

//Define a new virtual variable member sumOfSales for the Account dimension
VariableMember #sumOfSales OF [d/Account]
//Calculate the sales amount of all products in 2018 January and write the value to sumOfSales
DATA([d/Account]=#sumOfSales, [d/Product]="#") = RESULTLOOKUP([d/Account]="SALES")
//If the total sales amount is more than 1000, returns 10% sales rebate.
IF RESULTLOOKUP([d/Account]=#sumOfSales, [d/Product]="#")  > 1000 THEN
    DATA([d/Account]="REBATE") = RESULTLOOKUP([d/Account]="SALES")*0.1    
ENDIF

When comparing the VariableMember function with number variables, we recommend you use the VariableMember function when you want to temporarily store multiple values from different point of view. For detailed information about this, refer to Best Practice Tips for Performance.

Number Variables Function

Note

The INTEGER data type stores a 32-bit signed integer. The minimum value is -2,147,483,648 and the maximum value is 2,147,483,647.

The FLOAT data type specifies a 64-bit real number. The minimum value is -1.7976931348623157E308 and the maximum value is 1.7976931348623157E308.

Type

Syntax

Meaning

Examples & Remarks

FLOAT

FLOAT @[fVariable]

Defines a variable to be used in the script with a floating-point data type.

Sample Code
FLOAT @SumOfValue
FOREACH PRODUCT
	@SumofValue = @SumofValue + RESULTLOOKUP([d/ACCOUNT] = "Profit")
ENDFOR
DATA([d/Account] = "TotalProfit") = @SumofValue

Remarks:

  • All variables must start with @. And all variables will be listed after you enter @.

  • Variable definition should be added on the top of script under the global configuration settings.

  • Default value of FLOAT is 0.0

  • Not case sensitive: FLOAT @fAmount equals FLOAT @FAMOUNT

INTEGER

INTEGER @[iCount]

Defines a variable to be used in the script with an integer numeric type.

Sample Code
INTEGER @iCOUNT

IF [d/S_ACCOUT].[p/GROUP] = "DEPRECIATION" THEN
FOREACH
 @iCOUNT = @iCOUNT + 1
ENDFOR
ENDIF

Remarks:

  • All variables must start with @. And all variables will be listed after you enter @.

  • Variable definition should be added on the top of script under the global configuration settings.

  • Default value of INTEGER is 0

  • Not case sensitive: INTEGER @iCount equals FLOAT @ICOUNT

  • Integer variable cannot be assign to DATA directly.

When comparing the VariableMember function with number variables, we recommend you use the VariableMember function when you want to temporarily store multiple values from different point of view. For detailed information about this, refer to Best Practice Tips for Performance.

Logical Instructions

These keywords let you set conditions on a group of statements so that they only run in specific cases.

Logical Instructions

Description

Syntax

Remarks

IF ELSEIF ELSE

Conditionally executes a group of statements, depending on the value of an expression.

IF condition1

THEN

[statement1…]

ELSEIF condition2

THEN

[statement2…]

ELSE

[statement3…]

ENDIF

  • NULL values are available, for example:

    IF RESULTLOOKUP ([d/Account] ="REVENUE", [d/PRODUCT] ="#") != NULL THEN

    However, you can improve performance by avoiding = NULL comparisons. See Optimize Advanced Formulas for Better Performance for details.

  • You can use the following objects in an IF condition. By combining them using OR or AND operators, you can create complex conditions:
    • Multiple dimension filters (member IDs and properties)
    • Multiple cell value filters (RESULTLOOKUP and LINK)
    • Number and number variables (INTEGER and FLOAT)
    • External parameters
    • Functions that return a number (time calculation functions, ATTRIBUTE, mathematical functions, conversion functions)
    • Functions that return a date format (time/period scope selection functions)
    • Member selector functions (BASEMEMBER)
    For example:
    Sample Code
    IF [d/ACCOUNT]="PRICE" AND RESULTLOOKUP([d/ACCOUNT]="PRICE") >0 THEN
    //STATEMENT
    ENDIF
  • You can also group conditions using parentheses, for example:
    Sample Code
    IF ([d/CostCenter]="Korea" OR [d/ProfitCenter]="Japan") AND [d/Branch]="Seoul" AND RESULTLOOKUP() > 1000 THEN
  • Script’s performance will be severely impacted if you create a large calculation scope for ELSE.

    For example:

    Original Script

    Improved Script

    The statement in the ELSE below will generate a large calculation scope:

    IF [d/ACCOUNT]="PRICE" THEN

    ELSEIF RESULTLOOKUP([d/ACCOUNT]="PRICE")>0 THEN

    ELSE

    DATA()=100

    ENDIF

    Try reducing the calculation scope if possible:

    IF [d/ACCOUNT]="PRICE" THEN

    ELSEIF RESULTLOOKUP([d/ACCOUNT]="PRICE")>0 THEN

    ELSE

    DATA([d/ENTITY]="AsiaPacific", [d/Product]="Product1")=100

    ENDIF

  • After the linked model scope is defined, you can also use dimensions from a linked model in this function. For example:
    Sample Code
    MODEL [Sales]
    	…
    ENDMODEL
    
    IF [Sales].[d/Product_Item] = "SKU_001" THEN
    	//Statement...
    ENDIF

More examples:

IF with dimension filters

Sample Code
IF [d/CostCenter]="EMEA"  AND [d/ProfitCenter]="Germany" THEN
   //Statement...
ENDIF	

IF [d/FLOW] = (BASEMEMBER([d/FLOW].[h/Hierarchy] ,"F_TOTAL" )) THEN
  //Statement...
ENDIF

IF in IF function

Sample Code
IF [d/ACCOUNT] ="REVENUE" THEN
   IF [d/PRODUCT] ="16GB" THEN
   //Statement...
   ELSEIF [d/PRODUCT] = "32GB" THEN
   //Statement...
   ENDIF
ENDIF

IF with attribute

Sample Code
IF [d/ACCOUNT].[p/ACCTYPE]=("AST", "LEQ") THEN
  //Statement...
ENDIF
Sample Code
IF ATTRIBUTE([d/Product].[p/Factor]) > 2000 THEN     
    //Statement...
ENDIF
Sample Code
IF RESULTLOOKUP() < ATTRIBUTE([d/Product].[p/Factor]) THEN
    //Statement...
ENDIF
Sample Code
// If the dimension's attribute is of numeric (integer or decimal) type.

IF [d/Product].[p/Factor] > 2000 + %numberParameter% THEN
	//Statement...
ENDIF

IF [d/Product].[p/Factor] = (5, @Factor, %numberParameter%) THEN
	//Statement...
ENDIF

IF with cell value filters

Sample Code
IF RESULTLOOKUP([d/ACCOUNT]="PRICE") > 0 THEN
  //Statement...
ENDIF
Sample Code
IF RESULTLOOKUP() > 1000 OR RESULTLOOKUP() < 500 THEN
  //Statement...
ENDIF

IF with numbers or number variables

Sample Code
IF @amount > 100 OR @average = 90 THEN
	//Statement...
ENDIF

IF with functions that return the date format

Sample Code
IF [d/Date] = PERIOD("20210304") OR TODAY()="20210101" THEN
   //Statement...
ENDIF

IF with external parameter

Sample Code
IF [d/ProfitCenter]=%ProfitMember% AND RESULTLOOKUP() > 500 THEN
   //Statement...
ENDIF

IF with functions that return numbers

IF with functions that return numbers
Sample Code
IF DAY([d/Date]) > 15 OR DAYSINMONTH([D/Start_Date] > 5 THEN
   //Statement...
ENDIF

IF with member selector function

Sample Code
IF [d/Region]= BASEMEMBER([d/Region] ,"ASIA") THEN
   //Statement...
ENDIF

IF with filters from a linked model

After the linked model scope is defined, IF can also be used with filters of dimensions from a linked model or cell value filters associated with a linked model to produce subset of the linked model scope.

Here is an example of using IF together with filters of dimensions from a linked model.

The quantity of products produced by a factory in Bangkok is listed in the table below, which is built based on the model BOM, which is the linked model in this case.

Then write the following script to write quantity data to the model Production, which is the default model.

Sample Code
MODEL [BOM]
	MEMBERSET [d/Date] = "201901" TO "202105"	//Valid From 
	MEMBERSET [d/ValidTo] = "202101" TO "220012"	//Valid To
ENDMODEL

IF DATEDIFF([d/Date], [BOM].[d/ValidTo], "MONTH") >= 0 THEN
	IF DATEDIFF([d/Date], [BOM].[d/Date], "MONTH") <= 0 THEN
		DATA([d/Product_Group] = "SKU") = LINK([BOM], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")
	ENDIF
ENDIF

In this example, there are two levels of IF conditions to filter appropriate scope of data reading from model BOM, which is the linked model.

In the first IF condition, dimension Date from the default model and dimension ValidTo from the linked model are compared.

In the second IF condition, dimension Date from the default model with its scope inherited from the results of the first IF condition and dimension Date (Valid to) of the linked model are compared. The scope of dimension Date of the default model thus produced will then be used in the DATA of the second IF block to receive data from the linked model.

The script generates the records below:

The following is an example of using IF with cell value filters associated with a linked model.

Two employees, Paul Bailey (I_000101) and Michael Ager (I_000102), are assigned to different sales channels and have their target sales and bonus planned. The actual bonus is calculated based on the actual sales quantity. If actual sales quantity is lower than planned (Actual < Plan01), the bonus will be decreased by 20 per quantity of sales shortfall. If actual sales quantity is higher than planned (Actual > Plan01), the bonus will be increased by 100 per quantity of extra sales.

Write the script below to calculate their bonus.

Sample Code
MEMBERSET [d/Date] = "202101" TO "202112"
MEMBERSET [d/Business_Unit] = "BU_SKU"
MEMBERSET [d/Entity] = "US"
MEMBERSET [d/Account_HR] = "Bonus"
MEMBERSET [d/Employee] = ("I_000101", "I_000102")
MEMBERSET [d/Department_HR] = ("Sales", "Technical")
MEMBERSET [d/Measures] = "Salary_Amount"
MODEL [Sales]
	MEMBERSET [d/Product_Group] = "SKU"
	MEMBERSET [d/Product_Item] = "SKU_001"
	MEMBERSET [d/Sales_Channel] = ("Direc", "Online")
ENDMODEL
VARIABLEMEMBER #OFFSET OF [d/Account_HR]
IF LINK([Sales], [d/Version] = "public.Actual", [d/Measures] = "Quantity") < LINK([Sales], [d/Version] = "public.Plan01", [d/Measures] = "Quantity") THEN
	DATA([d/Account_HR] = #OFFSET, [d/Department_HR] = [Sales].[d/Sales_Channel].[p/CONT_DEPART], [d/Employee] = [Sales].[d/Sales_Channel].[p/Assigned_Employee]) =
	((LINK([Sales], [d/Version] = "public.Plan01", [d/Measures] = "Quantity") - LINK([Sales], [d/Version] = "public.Actual", [d/Measures] = "Quantity")) *-1) / 5
	//Only dimension scope of the linked model that meets the first IF condition, which means actual sales is lower than planned sales, will be taken to be used to calculate the bonus of the underachiever in each month.
ELSEIF LINK([Sales], [d/Version] = "public.Actual", [d/Measures] = "Quantity") > LINK([Sales], [d/Version] = "public.Plan01", [d/Measures] = "Quantity") THEN
	DATA([d/Account_HR] = #OFFSET, [d/Department_HR] = [Sales].[d/Sales_Channel].[p/CONT_DEPART], [d/Employee] = [Sales].[d/ Sales_Channel].[p/Assigned_Employee]) =
	LINK([Sales], [d/Version] = "public.Actual", [d/Measures] = "Quantity") - LINK([Sales], [d/Version] = "public.Plan01", [d/Measures] = "Quantity")
	// Only dimension scope of the linked model that meets the first IF condition, which means actual sales is higher than planned sales, will be taken to be used to calculate the bonus of the overachiever in each month.
ENDIF
DATA() = RESULTLOOKUP([d/Version] = "public.Plan01") + (RESULTLOOKUP([d/Account_HR] = #OFFSET) * 100)

The script generates the records below:

The new ELSEIF behavior

Starting from version 2021.02, you will immediately get the new behavior of ELSEIF statements, which excludes the overlapping data scope between itself and the IF condition.

Example

Let's say you write a script as below:

Sample Code
MEMBERSET [d/Date] = ("201801","201812")
MEMBERSET [d/Region] = ("EUR","USA")
MEMBERSET [d/Flow] = ("Open","Close")

 IF [d/Date] = "201801" THEN
   DATA() = 100
   ELSEIF [d/Region] = "EUR" THEN
     DATA() = 200
   ELSEIF [d/Flow] = "Close" THEN
     DATA() = 300
   ELSE
     DATA() = 400
  ENDIF
Step by step illustration of the new ELSEIF behavior

Line of Script

New behavior (Calculation scope in IF/ELSEIF)

New behavior

First IF condition (Step 1):

IF [d/Date] = "201801" THEN

DATA() = 100

Date Region Flow Value
201801 EUR Close 100
201801 EUR Open 100
201801 USA Close 100
201801 USA Open 100
201812 EUR Close

201812 EUR Open

201812 USA Close

201812 USA Open

First ELSEIF condition (Step 2):

ELSEIF [d/Region] = "EUR" THEN

DATA() = 200

Date Region Flow Value
201801 EUR Close 100
201801 EUR Open 100
201801 USA Close 100
201801 USA Open 100
201812 EUR Close 200
201812 EUR Open 200
201812 USA Close

201812 USA Open

The ELSEIF excludes the calculation scope overlapping with the previous IF.

Excluded overlapping scope:

[d/Date]="201801" of the previous IF condition

Second ELSEIF condition (Step 3):

ELSEIF [d/Region] = "Close" THEN

DATA() = 300

Date Region Flow Value
201801 EUR Close 100
201801 EUR Open 100
201801 USA Close 100
201801 USA Open 100
201812 EUR Close 200
201812 EUR Open 200
201812 USA Close 300
201812 USA Open  

The ELSEIF excludes the calculation scope overlapping with the previous IF and first ELSEIF.

Excluded overlapping scope:

[d/Date]="201801" of the IF condition

[d/Region]="EUR" of the first ELSEIF

Last ELSE condition (Step 4):

ELSE

DATA() = 400

Date Region Flow Value
201801 EUR Close 100
201801 EUR Open 100
201801 USA Close 100
201801 USA Open 100
201812 EUR Close 200
201812 EUR Open 200
201812 USA Close 300
201812 USA Open 400

The ELSE uses the remaining scope of all previous calculations and excludes the overlapping scope with the IF, first ELSEIF and second ELSEIF conditions.

Excluded overlapping scope:

[d/Date]="201801" of the previous IF condition

[d/Region]="EUR" of the first ELSEIF

[d/Flow]="Close" of the second ELSEIF

 

Final calculated result:

Date Region Flow Value
201801 EUR Close 100
201801 EUR Open 100
201801 USA Close 100
201801 USA Open 100
201812 EUR Close 200
201812 EUR Open 200
201812 USA Close 300
201812 USA Open 400

Restrictions for IF functions

Restriction Example
For RESULTLOOKUP() functions in a model with measures, a measure needs to be included in the RESULTLOOKUP() function.
  • Valid:

    IF RESULTLOOKUP([d/Measures] = "Price", [d/Product_Group] = "AC") > 1000 THEN

  • Not valid:

    IF RESULTLOOKUP([d/Product_Group] = "AC") > 1000 THEN

Measure filters aren’t supported with the OR operator. IF [d/Measures] != "Quantity" OR [d/CostCenter] = "Germany" THEN
When you include a measure filter and a non-measure filter, ELSEIF clauses aren’t supported.

IF [d/Measures] = "Price" AND [d/CostCenter] = "Germany" THEN

//Statement…

ELSEIF [d/CostCenter] = ("Germany", "France") THEN

Comparisons (>, <, >=, and <=) with non-numeric values aren't supported. IF [d/DATE] > "2019-03-15" THEN
Comparisons between DAY() functions aren't supported. IF DAY() > DAY() THEN

Loop Instructions

These functions let you repeat one or more statements iteratively over a specific scope of dimension members, or a specified number of times.

Loop Instructions

Description

Syntax

Example & Remarks

FOREACH

Enables you to iterate statements over the FOREACH scope defined by one or several dimensions or dimension attributes.

FOREACH [d/dimensionName1] ASC/DESC, [d/dimensionName2].[d/AttributeName] ASC/DESC...

[statement]

ENDFOR

  • There should be at least one dimension or dimension attribute in the FOREACH scope.

  • If there's more than one dimension member in the FOREACH scope, a group of statements will be executed with every one of the combinations of given dimensions.

    For example, if dimensionName1 has three members and dimensionName2 has four members, the statement will be executed 12 times.

  • If there are lots of combinations in the FOREACH scope, you may encounter worse performance.

  • You can use keyword ASC or DESC to iterate statements over the FOREACH loop in ascending or descending order. If no keyword is defined, ASC will be used as default. Here are the rules.

    Used with dimensions (FOREACH [d/dimensionName] ASC/DESC):

    strings are sorted alphabetically based on the dimension attribute of member ID, which means FOREACH [d/dimensionName] ASC/DESC is equivalent to FOREACH [d/dimensionName][p/ID] ASC/DESC

    Used with dimension attributes (FOREACH [d/dimensionName][p/AttributeName] ASC/DESC):

    strings are sorted alphabetically while numbers are sorted numerically based on the dimension attribute specified.

  • Members with the same value of the dimension attribute used in FOREACH will be grouped together and iterated accordingly.

    Example:

    Before performing any calculations, the source data is as below:

    Account Cost Center Date Signed Value
    Payables Sales 202201 600
    Payables Logistics 202201 900
    Payables Sales 202202 700
    Payables Logistics 202202 800
    Payables Sales 202203 500
    Payables Logistics 202203 300
    Payables Sales 202301 700
    Payables Logistics 202301 800
    Payables Sales 202302 600
    Payables Logistics 202302 900
    Payables Sales 202303 500
    Payables Logistics 202303 300

    Write the script below:

    FOREACH [d/Date][p/Year]

    [statement]

    ENDFOR

    Because the dimension attribute of year is the same for all the members that fall in the same year, the grouping for the first iteration will be as below:

    Account Cost Center Date Signed Value
    Payables Sales 202201 600
    Payables Logistics 202201 900
    Payables Sales 202202 700
    Payables Logistics 202202 800
    Payables Sales 202203 500
    Payables Logistics 202203 300

    And the grouping for the second iteration will be as below:

    Account Cost Center Date Signed Value
    Payables Sales 202301 700
    Payables Logistics 202301 800
    Payables Sales 202302 600
    Payables Logistics 202302 900
    Payables Sales 202303 500
    Payables Logistics 202303 300
  • When a dimension attribute is used in FOREACH, members that have no value for this dimension attribute will be excluded from the FOREACH scope.

  • After the linked model is defined, dimensions or dimension attributes from the linked model can also be used in the FOREACH scope.

    Sample Code
    MODEL [BOM]
    	MEMBERSET [d/ValidTo] = "202101" TO "202105"
    ENDMODEL
    FOREACH [BOM].[d/ValidTo]
    	DATA([d/Date] = PREVIOUS(1, "MONTH", [BOM].[d/ValidTo])) = LINK([BOM], [d/Version] = "public.Plan01", [d/Date] = "202101", [d/Measures] = "Quantity")
    ENDFOR

    In this example, DATA will be executed 5 times because dimension ValidTo in the FOREACH scope is filtered in the linked model scope to contain 5 members (from 202101 to 202105). Meanwhile LINK in each loop will also be executed with five different members of dimension ValidTo.

FOREACH.BOOKED

You can choose to repeat statements only for each dimension combination in FOREACH that has values. This can save unnecessary looping and therefore enhance the performance.

FOREACH.BOOKED [d/dimensionName1] ASC/DESC, [d/dimensionName2][d/AttributeName] ASC/DESC...

[statement]

ENDFOR

Note
At least one dimension should be defined in the FOREACH.BOOKED scope.

Company A decides to plan 2019’s sales rebates in each month for each customer based on the sales amount of sweaters in 2018. Write the script below:

Sample Code
FLOAT @sumOfProduct
MEMBERSET [d/Date] = "201801" TO "201812"
MEMBERSET [d/Customer] = ("CUST001", "CUST002")
FOREACH.BOOKED [d/Customer], [d/Date]
	@sumOfProduct = RESULTLOOKUP([d/Account] = "SweaterRevenue")
	IF @sumOfProduct < 5000 THEN
		DATA([d/Account] = "SalesRebateRatio", [d/Date] = NEXT(12)) = 0.8
	ENDIF
ENDFOR

In 2018, sweaters were sold only in Q1 and Q4. By writing the FOREACH.BOOKED instruction, sales rebates will be looped only on Q1 and Q4 data.

FOR

Enables you to repeat a group of statements a specified number of times.

FOR counter=start TO end STEP step

[statement]

ENDFOR

Sample Code
INTEGER @counter
FOR @counter = 1 TO 10 STEP 2
	DATA() = RESULTLOOKUP() * 0.5
ENDFOR

In this example, @counter starts at 1 and increases by STEP value 2 each time through the loop until @counter is greater than 10. The loop runs 5 times.

Sample Code
INTEGER @counter
FOR @counter = 10 TO 1 STEP - 2
	DATA() = RESULTLOOKUP() * 0.5
ENDFOR

In this example, @counter starts at 10 and decreases by STEP value -2 each time through the loop until the @counter is less than 1. The loop runs 5 times.

The following is an example of simple depreciation expense calculation:

Sample Code
INTEGER @UsefulLife
@UsefulLife = ATTRIBUTE([d/Equipment].[p/Useful_Life])
FOR @counter = 1 TO @UsefulLife
	DATA([d/Account] = "Depreciation_Exp", [d/Date] = NEXT(@counter)) = RESULTLOOKUP([d/Account] = "Equipment_Cost") / @UsefulLife
ENDFOR

In this example, @counter starts at 1 and increases by 1 each time through the loop until @counter is greater than Useful Life of equipment.

Remarks:

  • Counter is an integer type variable.

  • Start and End are signed integer values or integer variable names.

  • The STEP definition is optional and set to 1 by default. It needs a signed integer value that isn’t 0 or NULL.

  • Number parameters can't be used directly. Instead, convert one with an INT() function and assign the value to a variable.

  • Use a BREAK statement to stop the loop when the given condition is met.

  • The maximum number of loops is set to 10,000 by default.

BREAK

Interrupts the execution of a FOREACH, FOREACH.BOOKED, or FOR loop when a specific condition is met, and skips directly to running the statements after ENDFOR. This statement is optional.

FOREACH [d/dimensionName1], [d/dimensionName2…]

[statement]

BREAK

ENDFOR

[statements]

FOR counter=start TO end STEP step

[statement]

BREAK

ENDFOR

[statements]

Note
The BREAK statement is only available inside a FOREACH, FOREACH.BOOKED, or FOR loop.
Say you want to calculate depreciation of a piece of equipment. You can use the BREAK keyword to stop accumulating depreciation expense when the equipment's residual value reaches zero:
Sample Code
FOREACH [d/Date]
	IF RESULTLOOKUP([d/Account] = "Residual_Value") <= 0 THEN
		BREAK
	ENDIF
	DATA([d/Account] = "Depreciation_Exp") = RESULTLOOKUP([d/Account] = "EquipmentCost") / ATTRIBUTE([d/Equipment].[p/Useful_Life])

	@Accumulate_Depreciation = @Accumulate_Depreciation + RESULTLOOKUP([d/Account] = "Depreciation_Exp")

	DATA([d/Account] = "Residual_Value") = RESULTLOOKUP([d/Account] = "EquipmentCost") - @Accumulate_Depreciation
ENDFOR

FOREACH Use Cases

The FOREACH function executes members in the loop period sequentially.

In the three examples below, you plan to carry out annual planning in two different ways:

  • Example 1: Calculate revenue amount with a 10% increase over the previous month.

  • Example 2: Calculate revenue amount with a 10% increase over the previous month, stopping before Revenue reaches 200.

  • Example 3: Calculate revenue amount with a 10% increase over the same month of the previous year.

Example 1. Advanced formulas script to perform a 10% increase over the previous month
Sample Code
MEMBERSET [d/DATE] ="201801" TO "201812"
MEMBERSET [d/ACCOUNT] ="REVENUE"

FOREACH [d/DATE]
    Data() = ResultLookup([d/DATE]=PREVIOUS(1)) * 1.1
ENDFOR 

Example 2. Advanced formulas script to perform a 10% increase over the previous month, stopping before Revenue reaches 200:
Sample Code
MEMBERSET [d/DATE] ="201801" TO "201812"
MEMBERSET [d/ACCOUNT] ="REVENUE"

FOREACH [d/DATE]
    IF ResultLookup([d/DATE]=PREVIOUS(1)) * 1.1 > 200
		BREAK
    ENDIF    
    Data() = ResultLookup([d/DATE]=PREVIOUS(1)) * 1.1
ENDFOR 
Results of the FOREACH calculation with a BREAK statement
With the BREAK statement, the FOREACH calculation stops running before Revenue reaches 200.

Example 3. Advance formulas script for a 10% increase over the same month of the previous year.

Sample Code
MEMBERSET [d/DATE] ="201801" TO "201812"
MEMBERSET [d/ACCOUNT] ="REVENUE"

Data() = ResultLookup([d/DATE]=PREVIOUS(12)) * 1.1

In this example, if you still use FOREACH to calculate the results and write the script incorrectly like this:

Sample Code
MEMBERSET [d/DATE] ="201801" TO "201812"
MEMBERSET [d/ACCOUNT] ="REVENUE"

FOREACH [d/DATE]
	Data() = ResultLookup([d/DATE]=PREVIOUS(12)) * 1.1
ENDFOR

Although it returns the same result as previous one, the unnecessary FOREACH function will cause the function to run many extra times and severely affect the performance.

Calculation Expressions

These expressions include functions for reading data within the default model or from a different model, and for carrying forward values over multiple time periods.

RESULTLOOKUP

This function returns a data set of corresponding, specified dimension values filtered by this formula.

Syntax:

RESULTLOOKUP([DimensionFilter1], [DimensionFilter2]…)

Typical example:

Sample Code
IF [d/S_ACCOUNT]="VOLUME" THEN
DATA([d/S_ACCOUNT]="PL_SALES") = RESULTLOOKUP() * RESULTLOOKUP([d/S_ACCOUNT]= "ZPRICE",[d/AUDIT_TRAIL]="AT_INPUT", [[d/Version]="Actual")
ENDIF

In this case, the total sales is calculated by multiplying the volume by the price defined in the actual planning version.

Remarks:

  • If you want to multiply several ResultLookup functions, only the signed data whose dimension members match with each other will be multiplied.
  • The ResultLookup function uses post-aggregation values. Values with same POV will be aggregated automatically.
  • If you define a version dimension in RESULTLOOKUP, a private version is NOT supported, so you'll need to specify a Public version. Also, you won't be able to use this version as the target version for the data action.
    Caution

    There are some specific restrictions when you want to copy data from one version to another in a model that has the currency conversion setting enabled.

    The restrictions apply when the source version is a public version in edit mode:

    Source Version Target Version Restriction

    Public (in edit mode)

    Default Currency only

    (LC, Currency Dimension not supported)

    Public

     

    Copy default currency value

    Public (in edit mode)

    Local Currency

    Private

    Local Currency 1 to 1 strict match
    Default Currency Default Currency 1 to 1 strict match
    Currency Dimension Currency Dimension 1 to 1 strict match

    We recommend you follow the rules when copying a public version to the target public or private version; otherwise, the copy may fail due to the currency inconsistency.

For more detailed cases, refer to Change Values in Advanced Formulas with DATA, RESULTLOOKUP, and LINK.

LINK Function

LINK functions support classic account models and models with measures (with or without an account dimension) as either the source or target model. You can copy across any of these different model types.

Syntax:

LINK( [LinkedModelName], [DimensionFilter1], [DimensionFilter2] …)

A model is available to be a linked model if:

  • The currency conversion setting (enabled or disabled) for the linked model is the same as for the current model.

  • The Date dimension in the linked model has the same granularity as the Date dimension in the default model.

  • Fiscal Year is either disabled in both dimensions, or enabled with the same month shift.

  • its planning Date dimension includes all the members (with identical ID and Date properties ) of the planning Date dimension of the default model when the planning Date dimension of either or both models is set to user managed

For the dimension filters of the linked model in a LINK function:

  • The Version dimension must be specified; for example, [d/Linked model Version]= "public.Plan01"

  • You can use a parameter to define a version dimension member.

  • When the source model is a model with measures, you must specify a single measure, for example: LINK([SourceMeasureModel],[d/Measures]="SourceCurrency")

  • If the dimension exists only in the linked model but not in the default model, which means it’s a missing dimension, one member must be selected for the dimension, or else the dimension is defined as the default model's dimension property. For example:

    • [d/SalesPlanningRegion]= "China"

    • [d/LinkedSalesPlanningRegion]= [d/DefaultCostCenter] . [p/Entity]

  • If the dimension exists in both the linked model and the default model, then you can either define its members in the LINK function or choose not to include it in the function's definition. If you don't define it, all the members must match between the source and target dimension.

Typical Examples:

By the end of May, the planning specialist wants to plan the oil price of each airport for the rest of this year. To predict the oil price, he wants to use the existing Singapore jet oil price prediction as the indicator.

The source data of the oil price of each airport during the past five months is as below:

He wants to calculate the indicator for the remaining months by dividing the estimated jet oil price of Singapore in each month by the actual jet oil price in May, which is respectively as below:

Actual jet oil price

Estimated jet oil price

Then write the advanced formulas script below:

Sample Code
MEMBERSET [d/Account] = "JetOilPrice"
MEMBERSET [d/Time] = [d/Version].[p/PlanFromDate] to [d/Version].[p/PlanToDate]
MEMBERSET [d/DataSrc] = "Input"

FOREACH [d/Time]
//first use the LINK function to calculate the indicator of each month by dividing the estimated jet oil price of Singapore Airline by the actual price, then multiple it by the actual oil price of each airport in May.
                         DATA() = RESULTLOOKUP([d/Time] = "201805", [d/Version] = "public.Actual")
                                        * LINK([Airline_Indicator_AF] ,[d/Version] = "public.Plan201806", [d/Account] = "Singapore-Jet Oil Price" )
                                         / LINK([Airline_Indicator_AF] ,[d/Version] = "public.Actual", [d/Account] = " Singapore-Jet Oil Price ", [d/Time] = "201805" )

After applying this data action to the source data, the plan specialist can see the planned oil price of each airport for the rest of this year as below:

After the linked model scope is defined, the LINK function can be used in a way to offer more functionality and flexibility to cross-model data actions.

The linked mode scope, which can also be called source model scope, determines the dimension members that will be inherited by the dimension filters in the LINK function and other functions like IF and FOREACH. Note that only missing dimensions can be used in the linked model scope, which can be defined using the following syntax.

Syntax:

Sample Code
MODEL [LinkedModelName]
	MEMBERSET [d/Dimension1] = …
	MEMBERSET [d/Dimension2] = …
              …
ENDMODEL

Not all missing dimensions need to be defined inside MODEL…ENDMODEL. If you don’t define a missing dimension this way, all its members will be used by the dimension filters in other functions such as LINK. In the LINK function itself, you don’t need to select one member for a missing dimension in the function, which means they can be omitted in the LINK function.

Typical Examples:

A sales manager wants to calculate the revenue of all product items sold in the US in 2021. To get the results, she sets up data models and creates a data action with advanced formula steps.

There’re two models, namely Sales, the linked model which stores sales data, and Financial, the default model to which he/she intends to write the calculated results.

Linked Model
Dimension Shared or not
Date Yes
Entity Yes
Product Group Yes
Product Item No
Sales Channel No
Default Model
Dimension Shared or not
Date Yes
Entity Yes
Product Group Yes
DataSrc No
Business Unit No

The source data of quantity and price across product items and sales channels from the linked model is as below.

She writes the advanced formulas script below:

Sample Code
MEMBERSET [d/Measures] = "Closing"
MEMBERSET [d/Date] = "202101" TO "202112"
MEMBERSET [d/Entity] = "US"
MEMBERSET [d/Business_Unit] = "BU_SKU"
MEMBERSET [d/Product_Group] = "SKU"

MODEL [Sales]
	MEMBERSET [d/Product_Item] = ("SKU_001", "SKU_002", "SKU_003", "SKU_004", "SKU_005")
	MEMBERSET [d/Sales_Channel] = ("Direc", "Distribution", "Online", "Others", "Retail")
	// Product_Item and Sales_Channel only exist in the linked model Sales but not in default model Financial. They are used to define the linked model scope.
ENDMODEL

DATA([d/Account_Financial] = "33001000", [d/DataSrc] = "DataSrc_Sales")
= LINK([Sales], [d/Measures] = "Quantity", [d/Version] = "public.Plan01")
* LINK([Sales], [d/Measures] = "Price", [d/Version] = "public.Plan01")
// 33001000 stands for Revenue Domestic-Product. Product_Item and Sales_Channel are omitted in the definition of LINK function. 

She gets the following calculated results of revenue that are written to the default model.

Attribute Instruction

Attribute Instruction

Description

Syntax

Remarks

Attribute

Returns values of a dimension attribute. The values must be numbers.

ATTRIBUTE([d/DimensionName].[p/AttributeName], "MemberName")

Note
  • DimensionName and AttributeName are required.

  • MemberName is optional.

  • The attribute values returned by this instruction must be numbers.

  • The attribute values returned by this instruction must use a period (.) as the decimal separator and a comma (,) as the thousand separator.

  • You can define more than one ATTRIBUTE function in the source scope, like a RESULTLOOKUP(), and place it freely. For example:

    (ATTRIBUTE() + RESULTLOOKUP()) * ATTRIBUTE() + ATTRIBUTE() /RESULTLOOKUP()

  • You cannot define an ATTRIBUTE instruction in MEMBERSET.

  • After the linked model scope is defined, you can also use dimensions from a linked model in this function. For example:
    Sample Code
    MODEL [Sales]
    	…
    ENDMODEL
    
    @Volume = ATTRIBUTE([Sales].[d/Product_Item].[p/ProductUnit], "ACAC")

Typical Example

Example 1:
Sample Code
MEMBERSET [d/product] = ("P001", "P002")
DATA() = ATTRIBUTE([d/product].[p/Volume])
Example 2:
Sample Code
MEMBERSET [d/product] = ("P001", "P002")
DATA() = ATTRIBUTE([d/product].[p/Volume], "P003")

The two examples above look similar but return different calculation results.

The Attribute instruction in the first example returns the Volume attribute values of all available Product dimension members, namely, P001 and P002.

The Attribute instruction in the second example only returns the value of the Volume attribute of the dimension member P003.

This happens because, when a member is specified in the Attribute instruction, Attribute will only take this member into account instead of the members defined in MEMBERSET.

Sample Code
@TotalVolume = ATTRIBUTE([d/product].[p/Volume])

In this ATTRIBUTE function, no specific product member name is specified. Therefore, all values returned by ATTRIBUTE will be summed up and written to the target variable @TotalVolume.

Carry Forward Function

This business function allows users to carry forward values to the opening amount of new periods. This is a very common way for business users to manage the cash flow of balance sheet statement accounts, or to track changes in headcount.

Before this function is introduced, users can leverage the FOREACH loop to realize the carry forward scenarios. The new carry forward function simplifies the code and more importantly, by owning an iterative calculation itself, users now don’t need to write a loop function, thus enhancing the code performance.

The carry forward function only changes the opening values and closing values (or an alternative target member, if you define one). Other dimension members (for example, Hires or Change) might be defined in the MEMBERSET or IF() statement, but the values of these members don’t change. This applies regardless of the GENERATE_UNBOOKED_DATA configuration setting.

Code Syntax
CARRYFORWARD([d/DimensionName], "OpeningMemberName", "ClosingMemberName", CalculationExpression, "TargetMemberName")
  • DimensionName: Name of the flow dimension that records the opening values, closing values, and any changes during a period.

  • OpeningMemberName: Flow dimension member name for opening balance.

  • ClosingMemberName: Flow dimension member name for closing balance.

  • CalculationExpression: Expression which is composed of the flow dimension members combined by the plus/minus operators. Only plus and minus signs are allowed.

  • TargetMemberName: Optional. If left empty, the default value will be the same as the ClosingMemberName. It’s the dimension member that you write calculation results to.

In a model with measures, you may want to use measures in your carry forward function. This lets you set up different exception aggregation types over time for the carry forward measures, for example FIRST for the opening measure and LAST for the closing measure. For example:
Sample Code
DATA() = CARRYFORWARD([d/Measures], "Opening", "Closing", "Opening" + "Increase" - "Decrease") 
Note
  • Carry forward functions always treat unbooked data as 0 value. See below for an example. The GENERATE_UNBOOKED_DATA configuration setting does not affect these functions.

  • The scope of date dimension is defined in MEMBERSET or IF condition. In a carry forward case, the dimension members defined should be consecutive members. For example, MEMBERSET [d/DATE] = ("201901", "201902","201904") is not valid.

  • External parameters aren't supported as the opening, closing, or target members.

Example

Without CARRYFORWARD function

Sample Code
MEMBERSET [d/DATE]="201901" TO "201912"

//Loop with the defined date dimension scope, in this case it is from 201901 to 201912
FOREACH [d/Date]
//Carry forward previous ending balance to current month's opening value
DATA([d/FLOW] =" OPENING ") = RESULTLOOKUP([d/FLOW] =" ENDING ", [d/Date] =Previous()) 
//Recalculate current period’s ending balance
DATA([d/FLOW] =" ENDING ") = RESULTLOOKUP([d/FLOW] =" OPENING ")+ RESULTLOOKUP([d/FLOW] =" CHANGE ")+ RESULTLOOKUP([d/FLOW] =" OTHERS ")

With CARRYFORWARD function

Sample Code
MEMBERSET [d/DATE]="201901" TO "201912"

//Since 201901, carry forward previous period’s ending balance to current month's opening. And recalculate current period’s ending balance by adding the new opening amount to the amount in CHANGE and OTHERS.
DATA() = CARRYFORWARD ([d/FLOW], "OPENING", "ENDING",  "OPENING" + "CHANGE" + "OTHERS")

In this case, the carry forward function makes the following changes:

Initial data
CARRYFORWARD results

Note that the carry forward function treats the unbooked Closing value for Dec 2018 as 0, so the initial Jan 2019 Opening balance is overwritten.

Example

This is a typical example of HR planning :

Sample Code
MEMBERSET [d/DATE]="201901" TO "201912"
MEMBERSET [d/ACCOUNT]="HEADCOUNT"

//Since 201901, carry forward previous period’s closing balance to current month's opening. And calculate each period’s new hires by substracting the new opening headcounts and termination numbers from the closing headcounts.
DATA() = CARRYFORWARD ([d/FLOW], "OPENING", "CLOSING", "HIRES", "CLOSING" - "OPENING" - "TERMINATIONS")

Write Back Functions

These functions let you write back or delete records where a specified condition is met.

DELETE Function

This function allows users to clear data facts within a certain data scope.

Function

Syntax

Example

Remarks

DELETE

DELETE([DimensionName1], [DimensionName2]...)

MEMBERSET [d/TIME] = "2018.01" TO "2018.12"

IF RESULTLOOKUP([d/Account]="Disposal") != 1 THEN

DATA([d/FLOW]="OPEN") = RESULTLOOKUP([d/FLOW]="CLOSE",[d/TIME]= Previous())

ELSEIF RESULTLOOKUP([d/Account]="Disposal") = 1 THEN

DELETE([d/FLOW]="OPEN")

ENDIF

  • The DimensionName is optional. It can be defined not only as specific members, but also as a member set in the format like [d/DimensionName].[p/AttributeName].

  • Measures are supported, for example DELETE([d/Measures] = "Measure1").

  • Variable members are not allowed to be defined in the function.

  • This function cannot be used in a BPC imported model.

DATA Function

This function allows you to write values and create records based on the scope. By default, DATA function first only cleans the target scope and updates it with a specific value or data filtered out by the RESULTLOOKUP function. If you want to append data to the target scope instead, you can extend the DATA function to DATA.APPEND.

Syntax:

DATA([ DimensionName1], [DimensionName2]…)

DATA.APPEND ([d/DimensionName1], [d/DimensionName2]…)

Remarks:

  • To avoid generating constant values in DATA(), all undefined dimension scopes must be defined in DATA(). Otherwise, the planning script cannot generate a data set scope for the target dimensions.
  • Version dimensions cannot be defined in DATA(). The target version is defined when a user chooses a specific version in the Data Action Trigger within a story.
  • At least one dimension must be defined in DATA.APPEND().
  • In a model with measures, you can include measures in DATA and RESULTLOOKUP functions. For example:
    Sample Code
    MEMBERSET [d/Date] = "202001" TO "202012"
    
    IF [d/Measures] = "Price" AND [d/CostCenter] = "Germany" THEN
    
    DATA([d/Measures] = "Amount") = RESULTLOOKUP([d/Measures] = "Quantity") * RESULTLOOKUP([d/Measures] = "Price")
    
    ENDIF

Example:

Before performing any calculations, the source data is as below:

Flow Date Value
OPENING 201801 50
DELTA 201801 30
OTHER 201801 20
TOTAL 201801 0

To calculate the total amount using DATA.APPEND, write the script below:

Sample Code
DATA.APPEND ([d/FLOW]="TOTAL ") = RESULTLOOKUP([d/FLOW]="OPENING")
 DATA.APPEND ([d/FLOW]="TOTAL") = RESULTLOOKUP([d/FLOW]="DELTA")
DATA.APPEND ([d/FLOW]="TOTAL") = RESULTLOOKUP([d/FLOW]="OTHER")

In the above script, the flow dimension member "TOTAL" will accumulate the value of the flow dimension members "OPENING", "DELTA", and "OTHER" and return a total amount 100.

However, if you replace the DATA.APPEND function above with DATA and write it like this:

Sample Code
DATA ([d/FLOW]="TOTAL") = RESULTLOOKUP([d/FLOW]="OPENING") //Returns 50
 DATA ([d/FLOW]="TOTAL") = RESULTLOOKUP([d/FLOW]="DELTA") //Returns 30
DATA ([d/FLOW]="TOTAL") = RESULTLOOKUP([d/FLOW]="OTHER") //Returns 20

The flow dimension member "TOTAL" will return the value of the last dimension member "OTHER" as 20.

After the linked model is defined, DATA can also be used with the LINK function. For example:
Sample Code
MODEL [HR]
	…
ENDMODEL
DATA([d/Measures] = "Closing", [d/Account_Fin] = [HR].[d/Account_HR].[p/GL_Acc]) = LINK([HR], [d/Measures] = "Salary_Amt", [d/Version] = "public.Plan01")

For more detailed cases, refer to Change Values in Advanced Formulas with DATA, RESULTLOOKUP, and LINK.

Other Basic Syntax Inherited from Account Formulas

Master Data Functions

Master Data Functions

Meaning

Example

[d/dimension name]

Returns the dimension in the square brackets starting with "d/" keyword. Measures are also supported in a model with measures.

[d/ENTITY]

[d/Measures]

[p/dimension property name]

Returns the dimension property in the square brackets starting with "p/" keyword.

[d/ENTITY].[p/CURRENCY]

"dimension member name"

Returns the dimension member name in the double quotation marks. In a model with measures, you can identify measures the same way.

  • Single dimension member name:

    "KR"

  • Multiple members require round brackets:

    ("KR", "CN", "DE")

Mathematical and Conditional Operators

Operators

Syntax

Meaning

Examples

Addition

A+B

Adds two numbers.

42 + 1337 returns 1379

Subtraction

A-B

Subtracts the second number from the first one.

1337 - 42 returns 1295

Multiplication

A*B

Multiplies two numbers.

4 * 6 returns 24

Division

A/B

Divides the first number by the second one.

12 / 3 returns 4

Unary Minus

-A

Changes the sign of the number that comes afterward.

-3 returns -3

5 + -3 returns 2

And

condition1 AND condition2

Returns true if both values are true; otherwise false.

1 = 1 and 2 = 2 returns true

1 = 1 and 2 = 3 returns false

Or

condition1 OR condition2

Returns true if one or both values are true; otherwise false.

Note

When using the operator Or in a IF condition, dimension member selection is not supported, while number variable is supported.

1 = 1 or 2 = 3 returns true

1 = 2 or 2 = 3 returns false

Equal

A=B

Returns true if the values are equal.

1 = 1 returns true

1 = 2 returns false

Greater Than

A>B

Returns true if the first value is greater than the second one.

1 > 1 returns false

2 > 1 returns true

Less Than

A<B

Returns true if the first value is less than the second one.

1 < 2 returns true

1 < 0 returns false

Greater Than or Equal

A>=B

Returns true if the first value is greater than or equal to the second one.

1 >= 1 returns true

2 >= 1 returns true

Less Than or Equal

A<=B

Returns true if the first value is less than or equal to the second one.

1 <= 1 returns true

1 <= 0 returns false

Not Equal

A!=B

Returns true if the values are not equal.

1 != 1 returns false

1 != 2 returns true

Mathematical Functions

Functions

Meaning

Examples

ABS

Returns the absolute value.

ABS(-11) returns 11

LOG

Returns the natural logarithm.

LOG(100) returns 4.605

LOG10

Returns the base 10 logarithm.

LOG10(100) returns 2

MOD

Returns the remainder after number1 is divided by the divisor number2. The result has the same sign as divisor.

MOD(15,2) returns 1

POWER

Returns the number1 raised to the power of number2.

POWER(2,3) returns 8

POWER(5,2) returns 25

SQRT

Returns square root.

SQRT(4) returns 2

Conversion Functions

Functions

Meanings

Examples

ROUND (number1, number2)

Rounds argument <number1> to a specified number <number2> of decimal places.

ROUND (14.832, 1) returns the value 14.8.

ROUND (14.82, 0) returns the value 15.

ROUND (14.832, -1) returns the value 10.

Remarks

RESULTLOOKUP() can be used in ROUND:

ROUND(RESULTLOOKUP([d/ACCOUNT]="UNIT"), 1)

FLOOR (number1, number2)

Returns the number that is the largest that can be found but is not greater than the entered number <number1> with specified number <number2> of decimal places.

Number2 is optional.

FLOOR(14.832,0) returns the value 14

FLOOR(14.832,1) returns the value 14.8

FLOOR(14.832,-1) returns the value 10

Remarks

RESULTLOOKUP() can be used in FlOOR:

FLOOR(RESULTLOOKUP([d/ACCOUNT]="UNIT"), 1)

CEIL (number1, number2)

Returns the number that is the smallest that can be found but is not less than the entered number <number1> with specified number <number2> of decimal places.

Number2 is optional.

CEIL(14.832,0) returns the value 15

CEIL(14.832,1) returns the value 14.9

CEIL(14.82,-1) returns the value 20

Remarks

RESULTLOOKUP() can be used in CEIL:

CEIL(RESULTLOOKUP([d/ACCOUNT]="UNIT"), 1)

TRUNC (number1, number2)

Returns a numeric value <number1> truncated to a specific number <number2> of decimal places.

TRUNC (14.281, 1) returns the value 14.2

Remarks

RESULTLOOKUP() can be used in TRUNC:

TRUNC(RESULTLOOKUP([d/ACCOUNT]="UNIT"), 1)

FLOAT(number1)

Returns the floating point number.

FLOAT(14) returns 14.00

FLOAT(-14) returns -14.00

Remarks

RESULTLOOKUP() can be used in FLOAT:

FLOAT(RESULTLOOKUP([d/Account]="Price"))

INT(number1)

Returns the integer portion of a number.

INT(9.5) returns 9

INT(-9.5) returns -9

INT(0.5) returns 0

INT(-0.7) returns 0

Remarks

RESULTLOOKUP() can be used in INT:

INT(RESULTLOOKUP([d/Account]="Price"))