Purpose
Hello everyone. I’m Amanda Murphy, Product Owner in SAP Analytics Cloud Modeling Calculations team. This blog introduces the latest Model Calculations features available in the New Model type as part of the QRC Q1 2023 release.
Overview
So far this year, 6 new formulas have been created in Modeling Calculations for the new model type, to bring feature parity between the new model type and Account models.
In addition to these new formulas, we have also made 2 improvements to existing functionality by:
So, let’s take a look at some of our new formulas, created specifically for the New Model type.
First up, is our 2 new formulas called ACCOUNTLOOKUP & MEASURELOOKUP. These formulas allow users to create restricted Calculated Measures using dimension of type Account, not currently available through traditional LOOKUP features.
In order, for the formula to be made available, the model must contain measures and accounts, as they cross reference each other.
ACCOUNTLOOKUP returns the value of a measure, filtered by an account member.
MEASURELOOKUP returns the value of an account member, filtered by a measure name.
In the sample screenshot we can see a MEASURELOOKUP using GROSS & QUANTITY, which returns the cell value of GROSS where the measure member is QUANTITY.
Similarly, we can swap the values around using an ACCOUNTLOOKUP with QUANTITY & GROSS, which returns the cell value of QUANTITY where the account member is GROSS.
To sum up, ACCOUNTLOOKUP & MEASURELOOKUP formulas allow modellers to perform LOOKUPS on Accounts, previously not available until now.
The ITERATE function creates a rolling calculation, retrieving the value of a base measure and incrementing its value based on the previous iteration.
In the screenshot below, we can see an example of the ITERATE syntax, using a measure called Amount which is being incremented, the dimension is Date containing a Year property.
Inverse can also be included here to allow for planning enablement.
The results show the base value remains the same at 10, and each month is incremented by the value of the previous month.
The PRIOR() function is used in conjunction with ITERATE, used to retrieve the previous value of the member.
This formula can be used as an alternative to the Year Over Year nodes within the new Value Driver Trees within SAP Analytics Cloud Stories.
These are complex time-based formulas, previously only available for Account models. This is great example of how we are meeting feature parity requirement between Account models and the New Model type.
Until now, modellers were required to manually create numerous calculated measures in the new model type, to achieve the same results as YoY, SMA & CAGR in Account Models.
These new formulas enable the calculations to be created in a simpler way.
In the screenshot, we can see an example of Year Over Year, which calculates the difference between the measure LCAMOUNT in the current year compared to the previous year.
SMA calculates the Simple Moving Average of measure LCAMOUNT over the previous 3 years.
CAGR calculates the Compound Annual Growth Rate of measure LCAMOUNT over the 2-year period spanning the end of 2020 to the end of 2022.
Next, I would like to show you some new features that expand on existing functionality in the New Model type.
We have extended the Exception Aggregation feature to now allow for the use of Dimension Properties. This allows modellers to select specific dimension properties as part of an Exception Aggregation.
This in turn helps to optimize the calculation containing an exception aggregation by applying formula to selected properties of the dimension, instead of applying the formula to all members.
In the screenshot, we see the new behaviour, using a simple calculation containing a property. Previously, no value was seen in the Calculation Preview panel until the user selected an exception aggregation type and dimension from the properties panel.
Now, this is automatically filled for the modeller and users can select further exception aggregation dimension properties from the multi-select dialog that appears, when modellers click on the cube icon in the side panel.
To sum up, Exception Aggregation using Dimension Properties can help modellers to optimize their calculations. Note also, that in the SAP Analytics Cloud Guide, Exception Aggregation With Properties, we recommend that modellers keep their properties selection to the relevant ones that are needed to benefit from this new feature.
Last, but not least, is our recent changes to Add Missing Currency Rates.
In the past, when a modeller experienced a missing currency rates error message, they were required to locate the rates table separately and were also forced to populate the table with missing rates for ALL currencies, even when they were not needed.
Now, we have improved the Add Missing Rates feature, by creating a convenient link to the rates table from the model.
Within the rates table, modellers can populate missing rates for specific measures, using either a fixed currency or a currency from a dimension.
In the above screenshot, we can see the new link to the currency rates table, within the Calculation Preview panel of the model.
In the Add Missing Rates panel, we can see the new options to choose the Base Measure and Target Currency.
Modellers are guided to the correct measure containing missing rates and can toggle between a fixed target currency or a currency from a dimension.
The benefit for the modeller is that populating missing rates is now targeted to the rates that they need, and the requirement to populate ALL missing currencies is now removed.
Six new formulas have been created in Modeling Calculations for the New Model type, to bring feature parity between the new model type and Account models.
We have also made improvements to existing functionality by
SAP Analytics Cloud aims to continue to reach feature parity in Model Calculations between Account models and the New Model type and also between Modeling and Stories Calculations.
There are plenty more innovative new Model Calculations features to come in 2023, so watch this space!
If you have any questions, feel free to comment below or post a question to our SAP Analytics Cloud Questions & Answers forum.
See this SAP Knowledge Base Article if you’d like to learn more about FastTrack and Quarterly Release Cycle releases.
For further information, visit our SAP Analytics Cloud Community pages to find more product information, best practices, and more. And check out our SAP Road Map Explorer to see more upcoming features of SAP Analytics Cloud.
Thanks for reading.
Best regards,
Amanda Murphy
Hello everyone. I’m Amanda Murphy, Product Owner in SAP Analytics Cloud Modeling Calculations team. This blog introduces the latest Model Calculations features available in the New Model type as part of the QRC Q1 2023 release.
Overview
So far this year, 6 new formulas have been created in Modeling Calculations for the new model type, to bring feature parity between the new model type and Account models.
- ACCOUNTLOOKUP & MEASURELOOKUP
- ITERATE
- YOY, SMA, CAGR
In addition to these new formulas, we have also made 2 improvements to existing functionality by:
- Extending Exception Aggregation to allow the use of Dimension Properties
- Improving the Add Missing Currency Rates feature.
So, let’s take a look at some of our new formulas, created specifically for the New Model type.
- ACCOUNTLOOKUP & MEASURELOOKUP
First up, is our 2 new formulas called ACCOUNTLOOKUP & MEASURELOOKUP. These formulas allow users to create restricted Calculated Measures using dimension of type Account, not currently available through traditional LOOKUP features.
In order, for the formula to be made available, the model must contain measures and accounts, as they cross reference each other.
ACCOUNTLOOKUP returns the value of a measure, filtered by an account member.
MEASURELOOKUP returns the value of an account member, filtered by a measure name.
MEASURELOOKUP in the New Model type
In the sample screenshot we can see a MEASURELOOKUP using GROSS & QUANTITY, which returns the cell value of GROSS where the measure member is QUANTITY.
Similarly, we can swap the values around using an ACCOUNTLOOKUP with QUANTITY & GROSS, which returns the cell value of QUANTITY where the account member is GROSS.
To sum up, ACCOUNTLOOKUP & MEASURELOOKUP formulas allow modellers to perform LOOKUPS on Accounts, previously not available until now.
2. ITERATE
The ITERATE function creates a rolling calculation, retrieving the value of a base measure and incrementing its value based on the previous iteration.
In the screenshot below, we can see an example of the ITERATE syntax, using a measure called Amount which is being incremented, the dimension is Date containing a Year property.
ITERATE Formula in the New Model type
Inverse can also be included here to allow for planning enablement.
The results show the base value remains the same at 10, and each month is incremented by the value of the previous month.
The PRIOR() function is used in conjunction with ITERATE, used to retrieve the previous value of the member.
This formula can be used as an alternative to the Year Over Year nodes within the new Value Driver Trees within SAP Analytics Cloud Stories.
3. YOY (Year Over Year), SMA (Simple Moving Average) & CAGR (Compound Annual Growth Rate)
These are complex time-based formulas, previously only available for Account models. This is great example of how we are meeting feature parity requirement between Account models and the New Model type.
Until now, modellers were required to manually create numerous calculated measures in the new model type, to achieve the same results as YoY, SMA & CAGR in Account Models.
These new formulas enable the calculations to be created in a simpler way.
YOY, SMA, CAGR formulas in the New Model
In the screenshot, we can see an example of Year Over Year, which calculates the difference between the measure LCAMOUNT in the current year compared to the previous year.
SMA calculates the Simple Moving Average of measure LCAMOUNT over the previous 3 years.
CAGR calculates the Compound Annual Growth Rate of measure LCAMOUNT over the 2-year period spanning the end of 2020 to the end of 2022.
Next, I would like to show you some new features that expand on existing functionality in the New Model type.
4. Exception Aggregation With Dimension Properties
We have extended the Exception Aggregation feature to now allow for the use of Dimension Properties. This allows modellers to select specific dimension properties as part of an Exception Aggregation.
This in turn helps to optimize the calculation containing an exception aggregation by applying formula to selected properties of the dimension, instead of applying the formula to all members.
Exception Aggregation Containing Dimension Properties
In the screenshot, we see the new behaviour, using a simple calculation containing a property. Previously, no value was seen in the Calculation Preview panel until the user selected an exception aggregation type and dimension from the properties panel.
Now, this is automatically filled for the modeller and users can select further exception aggregation dimension properties from the multi-select dialog that appears, when modellers click on the cube icon in the side panel.
To sum up, Exception Aggregation using Dimension Properties can help modellers to optimize their calculations. Note also, that in the SAP Analytics Cloud Guide, Exception Aggregation With Properties, we recommend that modellers keep their properties selection to the relevant ones that are needed to benefit from this new feature.
5. Add Missing Currency Rates
Last, but not least, is our recent changes to Add Missing Currency Rates.
In the past, when a modeller experienced a missing currency rates error message, they were required to locate the rates table separately and were also forced to populate the table with missing rates for ALL currencies, even when they were not needed.
Now, we have improved the Add Missing Rates feature, by creating a convenient link to the rates table from the model.
Within the rates table, modellers can populate missing rates for specific measures, using either a fixed currency or a currency from a dimension.
Link to Rates Table from Model
In the above screenshot, we can see the new link to the currency rates table, within the Calculation Preview panel of the model.
New Base Measure & Target Currency Options in the Add Missing Rates Panel
In the Add Missing Rates panel, we can see the new options to choose the Base Measure and Target Currency.
Modellers are guided to the correct measure containing missing rates and can toggle between a fixed target currency or a currency from a dimension.
The benefit for the modeller is that populating missing rates is now targeted to the rates that they need, and the requirement to populate ALL missing currencies is now removed.
Conclusion
Six new formulas have been created in Modeling Calculations for the New Model type, to bring feature parity between the new model type and Account models.
- ACCOUNTLOOKUP & MEASURELOOKUP
- ITERATE
- YOY, SMA, CAGR
We have also made improvements to existing functionality by
- Extending Exception Aggregation to allow the use of dimension properties to help to optimize calculations
- Allowing modellers to populate missing rates that are more targetted, with our Add Missing Currency Rates enhancement.
SAP Analytics Cloud aims to continue to reach feature parity in Model Calculations between Account models and the New Model type and also between Modeling and Stories Calculations.
There are plenty more innovative new Model Calculations features to come in 2023, so watch this space!
If you have any questions, feel free to comment below or post a question to our SAP Analytics Cloud Questions & Answers forum.
See this SAP Knowledge Base Article if you’d like to learn more about FastTrack and Quarterly Release Cycle releases.
For further information, visit our SAP Analytics Cloud Community pages to find more product information, best practices, and more. And check out our SAP Road Map Explorer to see more upcoming features of SAP Analytics Cloud.
Thanks for reading.
Best regards,
Amanda Murphy
- SAP Managed Tags:
1 Comment
