About Script Formulas and Calculations in Advanced Formulas for Planning

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.

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)

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.

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.

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

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

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.

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.

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.

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.

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.

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

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.

• MEMBERSET [d/PRODUCT].[p/FACTOR] = 100 TO 300
• Set the scope of the Product dimension's Factor from 100 to 300.

• 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.

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.

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.

//The granularity here is year.

MEMBERSET [d/Time] = BASEMEMBER([d/Time], "[2017]")
IF [d/Time] = BASEMEMBER([d/Time], "[2017]") THEN

//The granularity here is quarter.

MEMBERSET [d/Time] = BASEMEMBER([d/Time], "[2017].[20173]")
IF [d/Time] = BASEMEMBER([d/Time], "[2017].[20173]") THEN

//The granularity here is month.

MEMBERSET [d/Time] = BASEMEMBER([d/Time], "[2017].[20173].[201709]")
IF [d/Time] = BASEMEMBER([d/Time], "[2017].[20173].[201709]") THEN

MODEL [HR]
	MEMBERSET [d/Account_HR] = BASEMEMBER([d/Account_HR].[h/parentId], "Salary_Month_Total")
ENDMODEL

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])

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.

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]

For each aggregated dimension from the default model, you’ll also need to specify a leaf member using 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] = "#"

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:

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") 

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]

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.

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.

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

INTEGER @iCOUNT

IF [d/S_ACCOUT].[p/GROUP] = "DEPRECIATION" THEN
FOREACH
 @iCOUNT = @iCOUNT + 1
ENDFOR
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.

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.

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.

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:

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

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.

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

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

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 

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.

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:

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.

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

Syntax:

RESULTLOOKUP([DimensionFilter1], [DimensionFilter2]…)

Typical example:

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 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:

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:

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.

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:

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.

Typical Example

MEMBERSET [d/product] = ("P001", "P002")
DATA() = ATTRIBUTE([d/product].[p/Volume])

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.

@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.

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.

CARRYFORWARD([d/DimensionName], "OpeningMemberName", "ClosingMemberName", CalculationExpression, "TargetMemberName")

DATA() = CARRYFORWARD([d/Measures], "Opening", "Closing", "Opening" + "Increase" - "Decrease") 

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.

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")

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

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:

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

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:

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.

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.