How to consume calculated member in Advanced Formulas.

How to consume calculated member in Advanced Formulas.
Estimated read time 9 min read

Introduction

The capability to read calculated members in Advanced Formulas relies on SAP HANA Cloud.

 
SAP Analytics Cloud tenants built on the SAP HANA Cloud platform

The following configuration determines whether the values of calculated members, including non-leaf members, account members with formulas, and calculated measures, can be read within the Advanced Formulas script.

CONFIG.READ_CALUCATED_MEMBER_VALUES = ON 

❕Note: How can you see whether your landscape runs on SAP HANA Cloud?
    – Check the “System About” dialogue in your SAC tenant.
    – Tenant running on SAP HANA Cloud will show the HANA Cloud version.

    

 
SAP Analytics Cloud tenants built on SAP HANA 2.0

The “Read Calculated Member” configuration” is not supported on SAP Analytics Cloud tenants built on the SAP HANA 2.0.

 

Workaround Solution

Here are some workaround solutions for reading calculated members in Advanced Formulas on SAP HANA 2.0.

1. Non-leaf members

Define the Non-leaf member using the VARIABLEMEMBER function and implement the standard aggregation script using the BASEMEMBER & “IF & ENDIF” function. Consequently, the value of the VARIABLEMEMBER is then calculated to be identical to that of the Formulas member.

❕Note: This workaround solution is applicable only for standard aggregation sums. All other cases are not supported.

 

Example

Requirement: Calculate the percentage of total revenue that North America and South America contribute.

– Entity dimension (hierarchy view) 

 

 

 

 

 

 

 

 

 

– Source Data

 

 

 

 

 

 

 

 

 

 

– Expected Result

 

 

 

 

 

– Advanced Formulas Script

 

MEMBERSET [d/Product] = “Prod_01”
MEMBERSET [d/Measures] = “Amount”
MEMBERSET [d/Account] = “Revenue”
MEMBERSET [d/Date] = “202410” TO “202412”

// Create a variable member for North America within the Entity dimension.
// Create another variable member for South America within the Entity dimension.
VARIABLEMEMBER #NorthAmerica_VM OF [d/Entity]
VARIABLEMEMBER #SouthAmerica_VM OF [d/Entity]

//Retrieve the corresponding data of base members under North America and write it into the scope of the North America variable member.
IF [d/Entity] = BASEMEMBER([d/Entity].[h/H1], “NorthAmerica”) THEN
DATA([d/Entity] = #NorthAmerica_VM) = RESULTLOOKUP()
ENDIF

//Retrieve the corresponding data of base members under South America and write it into the scope of the South America variable member.
IF [d/Entity] = BASEMEMBER([d/Entity].[h/H1], “SouthAmerica”) THEN
DATA([d/Entity] = #SouthAmerica_VM) = RESULTLOOKUP()
ENDIF

//Calculate the Sales Ratio for HQ North America.
//The calculation is based on the proportion of North America’s data relative to the sum of North and South America’s data.
DATA([d/Entity] = “HQ_NorthAmerica”, [d/Account] = “SalesRatio”)
= RESULTLOOKUP([d/Entity] = #NorthAmerica_VM) / (RESULTLOOKUP([d/Entity] = #NorthAmerica_VM) + RESULTLOOKUP([d/Entity] = #SouthAmerica_VM))

//Calculate the Sales Ratio for HQ South America.
//The calculation is based on the proportion of South America’s data relative to the sum of North and South America’s data.
DATA([d/Entity] = “HQ_SouthAmerica”, [d/Account] = “SalesRatio”)
= RESULTLOOKUP([d/Entity] = #SouthAmerica_VM) / (RESULTLOOKUP([d/Entity] = #NorthAmerica_VM) + RESULTLOOKUP([d/Entity] = #SouthAmerica_VM))

 

 

2. Account members with formulas

Define the Formula member using the VARIABLEMEMBER function, and implement the Account formulas logic using the Advanced formulas script. Consequently, the value of the Variablemember is then calculated to be identical to that of the Formulas member.

❕Note: This workaround solution is applicable only for calculating on leaf members; no other cases are supported.

 

Example

Requirement: Calculate the Net average price for each product.

– Account Dimension

  Member Formula: [Net_Revenue] = [Revenue] – [Rebate] – [Cash_Discount] 

– Source Data

 

– Expected Result

– Advanced Formulas Script

 

MEMBERSET [d/Measures] = “Amount”
MEMBERSET [d/Date] = “202410”
MEMBERSET [d/Entity] = “US”
MEMBERSET [d/Product] = (“Prod_01”, “Prod_02”, “Prod_03”, “Prod_04”)

//Create a variable member named #Net_Revenue_VM within the Account dimension.
VARIABLEMEMBER #Net_Revenue_VM OF [d/Account]

//Calculate the net revenue by subtracting the Sales Rebate and Cash Discount from the Revenue.
//Assign the result to the #Net_Revenue_VM variable member of the Account dimension.
DATA([d/Account] = #Net_Revenue_VM) = RESULTLOOKUP([d/Account] = “Revenue”) – RESULTLOOKUP([d/Account] = “Rebate”) – RESULTLOOKUP([d/Account] = “Cash_Discount”)

//Calculate the Net Average Price by dividing the value from the Net Revenue variable member by the Sales Quantity.
//Then write it into the Net Average Price account.
DATA([d/Account] = “Price_Avg”) = RESULTLOOKUP([d/Account] = #Net_Revenue_VM) / RESULTLOOKUP([d/Account] = “Quantity”)

 

 

3. Calculated measures

Define the Calculated Measure using the VARIABLEMEMBER function, and implement the Calculated Measure logic using Advanced formulas script. Consequently, the value of the Variablemember is then calculated to be identical to that of the Calculated Measure.

❕Note: This workaround solution is applicable only for calculating measures; no other cases are supported.

 

Example

Requirement: Calculate the average monthly sales for 2023 Actual and copy it to the 2024 Plan version.

– Measures

  Calculations: Sales = [Price] * [Quantity]

 

– Source Data

– Expected Result

– Advanced Formulas Script

 

MEMBERSET [d/Measures] = “Amount”
MEMBERSET [d/Date] = “202410”
MEMBERSET [d/Entity] = “US”
MEMBERSET [d/Product] = (“Prod_01”, “Prod_02”, “Prod_03”, “Prod_04”)

//Create a variable member named #Net_Revenue_VM within the Account dimension.
VARIABLEMEMBER #Net_Revenue_VM OF [d/Account]

//Calculate the net revenue by subtracting the Sales Rebate and Cash Discount from the Revenue.
//Assign the result to the #Net_Revenue_VM variable member of the Account dimension.
DATA([d/Account] = #Net_Revenue_VM) = RESULTLOOKUP([d/Account] = “Revenue”) – RESULTLOOKUP([d/Account] = “Rebate”) – RESULTLOOKUP([d/Account] = “Cash_Discount”)

//Calculate the Net Average Price by dividing the value from the Net Revenue variable member by the Sales Quantity.
//Then write it into the Net Average Price account.
DATA([d/Account] = “Price_Avg”) = RESULTLOOKUP([d/Account] = #Net_Revenue_VM) / RESULTLOOKUP([d/Account] = “Quantity”)

 

 

 

 

 

​ IntroductionThe capability to read calculated members in Advanced Formulas relies on SAP HANA Cloud. SAP Analytics Cloud tenants built on the SAP HANA Cloud platformThe following configuration determines whether the values of calculated members, including non-leaf members, account members with formulas, and calculated measures, can be read within the Advanced Formulas script.CONFIG.READ_CALUCATED_MEMBER_VALUES = ON ❕Note: How can you see whether your landscape runs on SAP HANA Cloud?    – Check the “System About” dialogue in your SAC tenant.    – Tenant running on SAP HANA Cloud will show the HANA Cloud version.     SAP Analytics Cloud tenants built on SAP HANA 2.0The “Read Calculated Member” configuration” is not supported on SAP Analytics Cloud tenants built on the SAP HANA 2.0. Workaround SolutionHere are some workaround solutions for reading calculated members in Advanced Formulas on SAP HANA 2.0.1. Non-leaf membersDefine the Non-leaf member using the VARIABLEMEMBER function and implement the standard aggregation script using the BASEMEMBER & “IF & ENDIF” function. Consequently, the value of the VARIABLEMEMBER is then calculated to be identical to that of the Formulas member.❕Note: This workaround solution is applicable only for standard aggregation sums. All other cases are not supported. ExampleRequirement: Calculate the percentage of total revenue that North America and South America contribute.- Entity dimension (hierarchy view)          – Source Data          – Expected Result     – Advanced Formulas Script MEMBERSET [d/Product] = “Prod_01”
MEMBERSET [d/Measures] = “Amount”
MEMBERSET [d/Account] = “Revenue”
MEMBERSET [d/Date] = “202410” TO “202412”

// Create a variable member for North America within the Entity dimension.
// Create another variable member for South America within the Entity dimension.
VARIABLEMEMBER #NorthAmerica_VM OF [d/Entity]
VARIABLEMEMBER #SouthAmerica_VM OF [d/Entity]

//Retrieve the corresponding data of base members under North America and write it into the scope of the North America variable member.
IF [d/Entity] = BASEMEMBER([d/Entity].[h/H1], “NorthAmerica”) THEN
DATA([d/Entity] = #NorthAmerica_VM) = RESULTLOOKUP()
ENDIF

//Retrieve the corresponding data of base members under South America and write it into the scope of the South America variable member.
IF [d/Entity] = BASEMEMBER([d/Entity].[h/H1], “SouthAmerica”) THEN
DATA([d/Entity] = #SouthAmerica_VM) = RESULTLOOKUP()
ENDIF

//Calculate the Sales Ratio for HQ North America.
//The calculation is based on the proportion of North America’s data relative to the sum of North and South America’s data.
DATA([d/Entity] = “HQ_NorthAmerica”, [d/Account] = “SalesRatio”)
= RESULTLOOKUP([d/Entity] = #NorthAmerica_VM) / (RESULTLOOKUP([d/Entity] = #NorthAmerica_VM) + RESULTLOOKUP([d/Entity] = #SouthAmerica_VM))

//Calculate the Sales Ratio for HQ South America.
//The calculation is based on the proportion of South America’s data relative to the sum of North and South America’s data.
DATA([d/Entity] = “HQ_SouthAmerica”, [d/Account] = “SalesRatio”)
= RESULTLOOKUP([d/Entity] = #SouthAmerica_VM) / (RESULTLOOKUP([d/Entity] = #NorthAmerica_VM) + RESULTLOOKUP([d/Entity] = #SouthAmerica_VM))  2. Account members with formulasDefine the Formula member using the VARIABLEMEMBER function, and implement the Account formulas logic using the Advanced formulas script. Consequently, the value of the Variablemember is then calculated to be identical to that of the Formulas member.❕Note: This workaround solution is applicable only for calculating on leaf members; no other cases are supported. ExampleRequirement: Calculate the Net average price for each product.- Account Dimension  Member Formula: [Net_Revenue] = [Revenue] – [Rebate] – [Cash_Discount] – Source Data – Expected Result- Advanced Formulas Script MEMBERSET [d/Measures] = “Amount”
MEMBERSET [d/Date] = “202410”
MEMBERSET [d/Entity] = “US”
MEMBERSET [d/Product] = (“Prod_01”, “Prod_02”, “Prod_03”, “Prod_04”)

//Create a variable member named #Net_Revenue_VM within the Account dimension.
VARIABLEMEMBER #Net_Revenue_VM OF [d/Account]

//Calculate the net revenue by subtracting the Sales Rebate and Cash Discount from the Revenue.
//Assign the result to the #Net_Revenue_VM variable member of the Account dimension.
DATA([d/Account] = #Net_Revenue_VM) = RESULTLOOKUP([d/Account] = “Revenue”) – RESULTLOOKUP([d/Account] = “Rebate”) – RESULTLOOKUP([d/Account] = “Cash_Discount”)

//Calculate the Net Average Price by dividing the value from the Net Revenue variable member by the Sales Quantity.
//Then write it into the Net Average Price account.
DATA([d/Account] = “Price_Avg”) = RESULTLOOKUP([d/Account] = #Net_Revenue_VM) / RESULTLOOKUP([d/Account] = “Quantity”)  3. Calculated measuresDefine the Calculated Measure using the VARIABLEMEMBER function, and implement the Calculated Measure logic using Advanced formulas script. Consequently, the value of the Variablemember is then calculated to be identical to that of the Calculated Measure.❕Note: This workaround solution is applicable only for calculating measures; no other cases are supported. ExampleRequirement: Calculate the average monthly sales for 2023 Actual and copy it to the 2024 Plan version.- Measures  Calculations: Sales = [Price] * [Quantity] – Source Data- Expected Result- Advanced Formulas Script MEMBERSET [d/Measures] = “Amount”
MEMBERSET [d/Date] = “202410”
MEMBERSET [d/Entity] = “US”
MEMBERSET [d/Product] = (“Prod_01”, “Prod_02”, “Prod_03”, “Prod_04”)

//Create a variable member named #Net_Revenue_VM within the Account dimension.
VARIABLEMEMBER #Net_Revenue_VM OF [d/Account]

//Calculate the net revenue by subtracting the Sales Rebate and Cash Discount from the Revenue.
//Assign the result to the #Net_Revenue_VM variable member of the Account dimension.
DATA([d/Account] = #Net_Revenue_VM) = RESULTLOOKUP([d/Account] = “Revenue”) – RESULTLOOKUP([d/Account] = “Rebate”) – RESULTLOOKUP([d/Account] = “Cash_Discount”)

//Calculate the Net Average Price by dividing the value from the Net Revenue variable member by the Sales Quantity.
//Then write it into the Net Average Price account.
DATA([d/Account] = “Price_Avg”) = RESULTLOOKUP([d/Account] = #Net_Revenue_VM) / RESULTLOOKUP([d/Account] = “Quantity”)       Read More Technology Blogs by SAP articles 

#SAP

#SAPTechnologyblog

Avatar for

You May Also Like

More From Author