Change Values in Advanced Formulas with DATA and RESULTLOOKUP

Use the function RESULTLOOKUP to filter out a certain range of data and use the DATA function to write them to other dimensions.

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: