Change Values in Advanced Formulas with DATA, RESULTLOOKUP, and LINK

Use the function RESULTLOOKUP to filter out a certain range of data and use the DATA function to write them to other dimensions. After the linked model scope is defined, you can also use the LINK function to filter out a certain range of data from the linked model and use the DATA function to write them to dimensions in the default model based on attributes of dimensions from the linked model.

Example 01: Copy with 5% Increase

You want to initialize "201801" plan data with "201701" with 5% increase. If there is no filters defined for the other dimensions, all REVENUE records of "201701" for each undefined dimension members will be copied to "201801" with a 5% increase.

//Set the scopes of CostCenter, Entity, Flow and Product dimensions using MEMBERSET
MEMBERSET[d/CostCenter] = "SALES"
MEMBERSET[d/Entity] = ("China","Korea","Japan") 
MEMBERSET[d/Flow] = "F_CLO"
MEMBERSET[d/Product] = ("Tennis Shoes","Running Shoes","Soccer Shoes") 

//Generate 2018 January revenue data by increasing 2017 January revenue by 5%
DATA ([d/Account]="Revenue", [d/Date]="201801") = 
RESULTLOOKUP ([d/Account]="Revenue", [d/Date]="201701") * 1.05

RESULTLOOKUP returns below records:

After multiplying the records returned by RESULTLOOKUP by 1.05, DATA generates new records based on the script.

Example 02: Calculate revenue amount

This script shows how to calculate and generate revenue by multiplying price by quantity.

//Set the scopes of CostCenter, Flow, Product and Date dimensions using MEMBERSET
MEMBERSET[d/CostCenter] = "SALES"
MEMBERSET[d/Flow] = "F_CLO"
MEMBERSET[d/Product] = ("Tennis Shoes","Running Shoes","Soccer Shoes") 
MEMBERSET[d/Date] = "201802"

//Generate 2018 February's sales revenue of tennis shoes, running shoes and soccer shoes respectively by multiplying price by quantity. 
DATA([d/Account] = "Revenue") =   RESULTLOOKUP([d/Account] = "Price", [d/Entity] = "#")* 
RESULTLOOKUP([d/Account] = "Quantity")

The RESULTLOOKUP returns below records:

According to the script, The two RESULTLOOKUP functions return below records respectively:

RESULTLOOKUP([d/Account] = "Price", [d/Entity] = "#")

RESULTLOOKUP([d/Account] = "Quantity")

After multiplying data in the two tables above, DATA generate below new record based on the script:

Example 03: Calculate revenue amount in a certain range of data

This script shows how to generate the revenue in a certain ranged data.

//Set the scopes of CostCenter, Flow, Product and Date dimensions using MEMBERSET
MEMBERSET[d/CostCenter] = "SALES" 
MEMBERSET[d/Flow] = "F_CLO"
MEMBERSET[d/Product] = ("Tennis Shoes","Running Shoes","Soccer Shoes") 
MEMBERSET[d/Date] = "201803"

//Generate 2013 March's sales revenue of the product tennis shoes, running shoes and soccer shoes respectively for Korea by multiplying the price of unassigned entity by the quantity sold in Korea. 
DATA([d/Account] = "Revenue",[d/Entity]="Korea") = RESULTLOOKUP([d/Account]="Price", [d/Entity]="#")* 
RESULTLOOKUP([d/Account] = "Quantity", [d/Entity]="Korea")

Revenue Amount = Sales Quantity * Unit Price

According to the script, The two RESULTLOOKUP functions return below records respectively:

RESULTLOOKUP([d/Account]="Price", [d/Entity]="#")

RESULTLOOKUP([d/Account] = "Quantity", [d/Entity]="Korea")

After multiplying data in the two tables above, DATA generate below new record based on the script:

Example 04: Advanced aggregation based on sister member

Aggregation Total Sales (Sales Domestic + Sales Foreign) and Total Sales Deduction (Sales Rebate + Sales Allowance)

This script shows that how to use a SisterMember property to aggregate the total sales and total sales deduction. In this case, Total Sales is the sister member of Sales Domestic and Sales Foreign; Total Sales Deduction is the sister member of Rebate and Sales Allowance.

//Set the scope of Account as base members of Net Revenue
MEMBERSET[d/Account] = BASEMEMBER([d/Account].[h/parentId] ,"Net Revenue")
MEMBERSET[d/CostCenter] = "SALES"
MEMBERSET[d/Entity] = "China"
MEMBERSET[d/Flow] = "F_CLO"
MEMBERSET[d/Product] ="Athletic Shirts"
MEMBERSET[d/Date] = "201804"

//Generate aggregated data by the sister member of the account dimension members in the defined scope
DATA([d/Account]=[d/Account].[p/SisterMember]) = RESULTLOOKUP()

Account dimension & SisterMember

The RESULTLOOKUP returns below records:

DATA generates below new record based on the script:

Example 05: Cross-Model Advanced aggregation based on attributes of dimensions from the linked model

Attributes of dimensions from the linked model can also be used to redirect the scope of the default model to which data is written. The following script shows how to use the attribute of the dimension Account_HR in the linked model HR to aggregate base payroll and other payroll of two employees and write them to the default model Financial.

MEMBERSET [d/Date] = "202101" TO "202112"
MEMBERSET [d/Business_Unit] = "BU_SKU"
MEMBERSET [d/Entity] = "US"
MODEL [HR]
	MEMBERSET [d/Account_HR] = BASEMEMBER([d/Account_HR].[h/parentId], "Salary_Month_Total")
ENDMODEL

DATA([d/Measures] = "Closing", [d/Account_Financial] = [HR].[d/Account_HR].[p/GL_Account]) =
LINK([HR], [d/Measures] = "Salary_Amount", [d/Version] = "public.Plan01")
//Read all accounts of Account_HR and aggregate their values to the related members "Payroll Base" and "Payroll Other" of Account_Financial.

In the linked model HR, salary data is scattered among different members of dimension Account_HR. GL_Account, its attribute, has two values, namely 42007100, which stands for Payroll Base, and 42007100, which stands for Payroll Other. Both are mapped to the dimension Account_Financial of the default model Financial. The LINK function returns the records below.

DATA generates the new records below based on the script. The values of Base Salary and Bonus are aggregated to their related member Payroll Base. The values of other accounts, including Pension, Health Insurance, Education, Retirement Fund and Commission, are aggregated to their related member Payroll Other.