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.
In this section, you'll learn how to define and use:
- Global configuration functions
- Member selector functions
- Member aggregation functions
- Time/Period scope selection functions
- Time calculation functions
- Virtual variable member
- Number variables functions
- Logical instructions
- Loop instructions
- DATA and ResultLookup functions
- Attribute instruction
- Delete function
- Linked model function
- Business function – carry forward function
- Master data functions
- Mathematical and conditional operators
- Mathematical functions
- Conversion functions
To learn about the syntax and semantics of advanced formula scripts, check out the SAP Analytics Cloud Advanced Formulas Reference Guide.
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.
1. MEMBERSET
Syntax:
MEMBERSET [d/DimensionName] = "DimensionMemberName"
MEMBERSET statements let you pick which members to include in your advanced formulas step. 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/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.
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:
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.
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.
- MEMBERSET[d/Date] = [d/Version].[p/Beginning] to [d/Version].[p/Ending]
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.
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.
- 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 [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.
2. 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.
//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
3. 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])
- 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 a 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.
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.
MEMBERSET [d/Product] = ("P1", "P2", "P3") DATA([d/Product] = "#") = RESULTLOOKUP([d/Date] = PREVIOUS(12))
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]
-
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:
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")
In this example, [d/CostCenter] and [d/ProfitCenter] can't be defined within the LINK formula, because they are set as aggregated dimensions.
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.
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]))
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. |
|
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. |
|
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”. |
|
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”. |
|
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”. |
|
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
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:
IF Dimension filter AND Dimension filter THEN // Supported.
This also applies to the time calculation functions, for example:
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:
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:
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:
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:
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:
Case 4: @Num = DAY([d/Time])*RESULTLOOKUP() Case 5: @Num = DAY([d/Entity].[p/StartDate]) *RESULTLOOKUP()
1. DAY, 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. |
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. |
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. |
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 number. 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. |
2. 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. |
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. |
3. 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
|
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. |
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
|
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 ) |
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]
- 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.
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.
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:
|
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:
|
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 |
Description |
Syntax |
Remarks |
||||
---|---|---|---|---|---|---|---|
IF ELSEIF ELSE |
Conditionally executes a group of statements, depending on the value of an expression. |
IF condition1 THEN ELSEIF condition2 THEN [statement2…] ELSE [statement3…] ENDIF |
|
More examples:
IF with dimension filters
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
IF [d/ACCOUNT] ="REVENUE" THEN IF [d/PRODUCT] ="16GB" THEN //Statement... ELSEIF [d/PRODUCT] = "32GB" THEN //Statement... ENDIF ENDIF
IF with attribute
IF [d/ACCOUNT].[p/ACCTYPE]=("AST", "LEQ") THEN //Statement... ENDIF
IF ATTRIBUTE([d/Product].[p/Factor]) > 2000 THEN //Statement... ENDIF
IF RESULTLOOKUP() < ATTRIBUTE([d/Product].[p/Factor]) THEN //Statement... ENDIF
// 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
IF RESULTLOOKUP([d/ACCOUNT]="PRICE") > 0 THEN //Statement... ENDIF
IF RESULTLOOKUP() > 1000 OR RESULTLOOKUP() < 500 THEN //Statement... ENDIF
IF with numbers or number variables
IF @amount > 100 OR @average = 90 THEN //Statement... ENDIF
IF with functions that return the date format
IF [d/Date] = PERIOD("20210304") OR TODAY()="20210101" THEN //Statement... ENDIF
IF with external parameter
IF [d/ProfitCenter]=%ProfitMember% AND RESULTLOOKUP() > 500 THEN //Statement... ENDIF
IF with functions that return numbers
IF DAY([d/Date]) > 15 OR DAYSINMONTH([D/Start_Date] > 5 THEN //Statement... ENDIF
IF with member selector function
IF [d/Region]= BASEMEMBER([d/Region] ,"ASIA") THEN //Statement... ENDIF
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.
Let's say you write a script as below:
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
Line of Script |
New behavior (Calculation scope in IF/ELSEIF) |
New behavior |
||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
First IF condition (Step 1): IF [d/Date] = "201801" THEN DATA() = 100 |
|
|||||||||||||||||||||||||||||||||||||
First ELSEIF condition (Step 2): ELSEIF [d/Region] = "EUR" THEN DATA() = 200 |
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 |
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 |
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:
|
Limitations for IF functions
Limitation | Example |
---|---|
For RESULTLOOKUP() functions in a model with measures, a measure needs to be included in the RESULTLOOKUP() function. |
|
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 |
Description |
Syntax |
Example & Remarks |
---|---|---|---|
FOREACH |
Enables you to iterate statements over the FOREACH scope defined by one or several dimensions. |
FOREACH [d/dimensionName1], [d/dimensionName2…] [statement] ENDFOR |
|
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], [d/DimensionName2]… 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:
|
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.
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
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.
RESULTLOOKUP
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.
- In RESULTLOOKUP, you cannot use a parameter to define a version dimension member.
- 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 and RESULTLOOKUP.
DATA
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:
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.
For more detailed cases, refer to Change Values in Advanced Formulas with DATA and RESULTLOOKUP.
Attribute Instruction |
Description |
Syntax |
Remarks |
---|---|---|---|
Attribute |
Returns values of a dimension attribute. The values must be numbers. |
ATTRIBUTE([d/DimensionName].[p/AttributeName], "MemberName") Note
|
|
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 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 LINK function is used to copy or read data from other linked models besides the default model in the data action. Because the LINK function is used to read data, it cannot be defined in DATA functions to write data. The model you write data to should be the default model you defined for the data action.
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.
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 cannot 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, 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:
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")
-
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.
DATA() = CARRYFORWARD([d/Measures], "Opening", "Closing", "Opening" + "Increase" - "Decrease")
-
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.
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:
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.
This is a typical example of HR planning :
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")
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. |
|
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. RemarksRESULTLOOKUP() 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 RemarksRESULTLOOKUP() 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 RemarksRESULTLOOKUP() 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 RemarksRESULTLOOKUP() 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 RemarksRESULTLOOKUP() 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 RemarksRESULTLOOKUP() can be used in INT: INT(RESULTLOOKUP([d/Account]="Price")) |