Lumira Measures

SAP Lumira – Calculations Best Practices

Version 1.2 April 2015 Andreas Krause, EMEA Center of Excellence for BI

SAP LUMIRA – LUMIRA – CALCULATIONS CALCULATIONS BEST PRACTICES

SAP LUMIRA – LUMIRA – CALCULATIONS CALCULATIONS BEST PRACTICES

Contents Grouping Groupin g Calculations Calculati ons .................. ................. .................. ................. .................. .................. ................. ........ 3 Time dependent Calculations ....................................................................................................................... 8 Currency Conversion Calculations .............................................................................................................. 21

Page 2

SAP LUMIRA – CALCULATIONS BEST PRACTICES


Grouping Calculations With SAP Lumira you have the ability to create groupings based on existing attributes. This is very useful especially when business users want to have another view on the data and want to have the measures aggregated on data organized in different ways as provided by the source system. In the example below you have the option to group existing values of a dimension with the grouping functionality available in with the manipulation tools.

With pushing the “Group by Selection” button an interface opens to group and organize the values on your requirements

Page 3



As a result the values are grouped and shown in a new dimension as illustrated in the next screen.

Page 4



Often there is also the requirement to group values of a dimension based on values of a measure. As example you want to group the values of the category dimension based on a range of the gross margin measure. You want to group the categories in a new category where in group A are the categories with the highest margin above 10 000 € , in group B are the categories within a range between 10000 and 1000 €, in group C are the categories between 1000 and 100€ and finally everything else below 100€ in group D. The example below now describes how to define the grouping to achieve the required groups of categories. To achieve this you need to define a new calculated dimension (in this example called “Margin Groups”) via the interface for creating a calculation.

Within the editor interface you need to apply formula based on the category dimension: if {Gross Margin} < 100 then "D" else if {Gross Margin} < 1000 then "C" else if {Gross Margin} < 10000 then "B" else "A" The formula groups the values based on different ranges e.g. for the group “D” all values below 100, for group “C” all values between 100-1000, for group “B” all values between 1000-10000 and finally for group “A” all values above 10000.

Page 5



After defining the formula the result should looks like follows:

After you created the calculated attribute you can show the categories belonging to the different A, B, C and D margin groups as required. In the example a tree-map visualization is used to show the members of the different groups

Page 6



In addition you can use the newly calculated attribute to create a new calculated measure defined as a “Count” to count all members belonging to the different margin groups.

With the calculated measure you have now in addition the option to count the number of members belonging to each margin group as shown below in the column chart.

Page 7



Time dependent Calculations Another important area is to be able to create time dependent calculations. A simple example is a required Year-to-date vs. Previous Year-to-date comparison of a measure. In the following section a possible formula is explained to create YTD and PYTD calculation. Again you can use the interface to create a new calculated dimension.

After opening the interface following formula is applied to calculate the Year-to-Date values: if Year({Date})=Year(CurrentDate()) and {Date} <=(CurrentDate()) then {Gross Margin} else 0 The formula compares the Year value from the date dimension in the dataset with the year value of the actual date which is available through the CurrentDate() function. In addition all dates which are smaller or equal to the current date are compared. With this comparison it is ensured that all datasets with a date older or equal to today are considered for the year-to-date calculation.

Page 8



One of the functionalities in SAP Lumira allows you to edit a new created Calculated Dimension. For this you just need to hit the preference button of the dimension in the dimension and select Edit. This allows you to edit an existing formula without the need to recreate it again as in older releases.

In the next step the Previous-Year-to-Date values are calculated with following formula: if Year({Date}) = Year(CurrentDate()) - 1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Gross Margin} else 0 The formula compares the Year value from the date dimension in the dataset with the year value of the actual date -1 year which is available through the CurrentDate()-1 function. In addition the month numbers which are smaller or equal to the month of the current date are compared. In the last part the day numbers of the previous year which are smaller or equal to the day numbers of the current year are compared. With this comparison it is ensured that all datasets with a date older or equa l to the same date last year are considered for the previous year-to-date calculation.

Page 9



Column YTD Gross Margin dimension shows only values up to the current date of 2013.

At the same time column PYTD Gross Margin dimension shows only values for 2012 up to the current date.

Page 10



To finally use the calculated values for visualizations you need to create a measures like shown in the screen below.

Now the YTD and PYTD measures are available and you can use it for comparison in a column chart.

Page 11



Beside Year-to-Date and Previous-Year-to-Date calculations it’s also important to get the calculations per Quarter or per Month. Below you can find the calculation formulas for these periods: Quarter-to-Date:

if Quarter({Date}) = Quarter(CurrentDate()) and {Date} <= (CurrentDate()) then {Gross Margin} else 0 Previous-Quarter-to-Date:

if Quarter({Date}) = Quarter(CurrentDate())-1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Gross Margin} else 0 Month-to-Date:

if Month({Date}) = Month(CurrentDate()) and {Date} <= (CurrentDate()) then {Gross Margin} else 0 P r e v i o u s M o n t h - t o - D a t e:

if Month({Date}) = Month(CurrentDate()) - 1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Gross Margin} else 0 The process to apply the formulas is the same as described above for the YTD and PYTD calculations. Another important use case is to compare data from two different time periods for example a Year over Year or Quarter over Quarter comparison. The goal is to compare the results between the different time periods. In the following example it is explained how to calculate a comparison of the Gross Margin between the different Quarters. Again you can use the formula editor and create first a Calculated Dimension

For each required Quarter you need to create a separate Calculation to only get the results of the specific Quarter. The formula to apply in this example looks follows: if {Year-Quarter} = "2014-Q1" then {Gross Margin} else 0

Page 12



As a result the column Gross Margin Q1 2014 only shows the values for Q1 2014.

You can repeat the calculation with the other required Quarters for the comparison. Gross Margin Q2 2014: if {Year-Quarter} = "2014-Q2" then {Gross Margin} else 0 Gross Margin Q3 2014: if {Year-Quarter} = "2014-Q3" then {Gross Margin} else 0 Gross Margin Q4 2014: if {Year-Quarter} = "2014-Q4" then {Gross Margin} else 0

Page 13



As a result you should have now four new Dimensions each with the values for its own Quarter

In the next steps you have to create a Calculated Measure out of each Dimension.

The result should show now four new Measures

Page 14



Now you can use the new Calculated Measures to show a comparison of the Gross Margin between the different time periods. The example above shows a simple bar chart displaying the Gross Margin of the four Quarters.

Additional it’s interesting to see the delta or variance between the different time periods for a certain dimension. In the example below you can see the 2014 Quarter over Quarter Gross Margin for the different Product Lines in a table view.

Page 15



The next step is to create a variance be tween each quarter. In the raw data column view below only the data for the calculated Quarter is available e.g. in the screenshot it’s only Gross Margin 2014-Q4 having numbers. All the other calculated quarters are showing 0.00 as value as they only have valid numbers for their calculated quarter. Lumira doesn’t support cell references or “IN” ca lculations in the formula language to transpose the values of each quarter side by side.

To achieve a variance calculation you need to use a different approach. A possible approach is to export the dataset from the table as a new dataset and map it against the Product Line Dimension in the original dataset. With the mapping of the dimension “Lines” and applying the filter again to 2014-Q4 it is possible to calculate the variance between the different quarters. Below you can find the step by step description how to achieve this. First step is to export the current table view as a new dataset

Page 16



Go back to the Prepare room to merge the exported dataset back to the original dataset.

In the Merge Data Dialog change the Lookup Dataset to the one with the exported dataset from the table visualization.

Afterwards you have to map the dimensions for Lines as the key columns to merge the exported dataset back into the original dataset

Page 17



Now the Gross Margins of 2014-Q1 – 2014-Q4 are available in the original dataset. As the mapping was done only on the Line dimension the values are only valid for exactly this dimension. There are no aggregations of the measures possible to other dimension levels or combination of dimensions. As you can see below one of the challenges is that all dimensions which enable a more granular level are going to duplicate the values e.g. for Product Line “Jackets” there are multiple Categories like “Boatwear” or “Fancy fabric” which are going to create wrong results when aggregating the measures again to the Line dimension level

To avoid this behavior the measures which are used now for a variance calculations must not be aggregated. To achieve this, you need to set the aggregation function for the measure to “none” as shown in the screenshot below.

With this setting the measure is no longer aggregated and when you now use the “Line” dimension the values of the merged measures are showing exactly the same values as the ones from the exported table.

Page 18



Table with exported measure values:

Table with original measure values:

As now every column of the merged measures has now a value for each cell, you can create a calculated dimension for the variance. The formula for calculating the variance (as percentage value) is following: {Gross Margin 2014-Q4_Sum} / {Gross Margin 2014-Q3_Sum} * 100

Page 19



After you created the calculated dimension the dimension has to be turned into a measure

Again, to avoid wrong aggregations and wrong values the aggregation has to be set to “None”

The result is then the variance between the different quarters e.g. here the variance of the Gross Margin between Q4 and Q3

Page 20



Currency Conversion Calculations A requested scenario is to setup currency conversion from Global currency to local currencies or vice versa. An option to include currency conversion in Lumira is to use the logic provided from the semantic layer of our SAP BusinessObjects BI 4 Suite or to leverage the currency conversion functionality from SAP HANA while connecting in online mode. Sometimes the possibility to convert the currency is simply not included in the semantic layer or in SAP HANA. In case Lumira is connected to local data sources it is not available at all so you need to define a solution within Lumira. To be able to provide currency conversion functionality the currency data needs to be available. A good source for currency codes and conversion rates you can found on http://www.oanda.com/currency/table. As shown on the screen below on this webpage you can define your base currency and get the actual conversion rates for selected (or all) currencies in the required format. In the following section the data is downloaded as a .csv file.

Page 21



To be able to use the currency table the base data set needs to have the local currencies included like shown in the next screen. The example shows the use of Euro as global and Dirham from the United Arab Emirates.

As first step the base data needs to be merged/joined with the currency table. For this you need to add the new data set into Lumira.

Then the data source type and file has to be selected.

Page 22



A preview of the selected data set is shown in the next screen

After adding the data set into Lumira the base dataset needs to be merged/joined with the currency table. For this you have to use the merge operation..

In the merge screen select the key columns to enable the join operation. The key columns in the example are the local currency column within the base dataset and the currency code from the currency table.

Page 23



The result of the merge operation you can see in the next screen. The new added columns are highlighted in red.

The next step is to apply the ca lculation for the currency conversion. For this you need to create a new calculated measure.

Column Units/ 1 EUR contains the currency rate factor from Euro to the local currency. The formula looks like: {Gross Margin} * {Units/1 EUR} With this formula the actual Gross Margin available in Euro is multiplied with the currency rate of the local currencies. In case the Gross Margin is only available in local currency and it’s required to calculate a common or company currency it is also possible to do this. As the currency table includes in addition the conversion rate from the local currencies (column EUR/1 Units) to Euro following formula can be applied: {Gross Margin} * {EUR/1 Units}

Page 24



The screenshot below shows the definition of the conversion from company currency to local currency.

The new created calculated measure called “Gross Margin in local currency” is now available in the measures panel.

Page 25



You can now use the measure to visualize the Gross Margin in local currency of the selected country like in the column chart below.

Page 26

© 2015 SAP SE or an SAP affiliate company. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an SAP affiliate company. SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. Please see http://www.sap.com/corporate-en/legal/copyright/index.epx#trademarkf or additional trademark information and notices. Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors. National product specifications may vary. These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP SE or its affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP SE or SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or any related presentation, or to develop or release any functionality mentioned therein. This document, or any related presentation, and SAP SE’s or its affiliated companies’ strategy and possible future developments, products, and/or platform directions and functionality are all subject to change and may be changed by SAP SE or its affiliated companies at any time for any reason without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.

Lumira Measures

Recommend Documents