Objective: Use the latest calculations available in SAP Analytics Cloud to expose additional information that was not available in your original data model. Understand the potential and workflow of creating calculations in our story.
Estimated Time: 5 mins
Exercise Description: You have access to shipping information and financial sales data that with some common dimensions, including Order Number and Store Name. You need to create various calculated dimensions, statistical aggregations to understand the effects of shipping times and impact of delivery delays on sales revenue..
Key Features:
- Create calculated dimensions with string functions to enhance your data
- Employ statistical calculations in aggregations of your data
- Integrate calculation input controls to simulate values in your visualizations
🚩Please make a copy of the dashboard "Exercise 2.1 Create Basic Calculations within Stories"
🚩When working with your data in SAP Analytics Cloud, you may find that you want to look at additional components to your data that are currently not captured by the existing measures and dimensions in the model. Luckily, story designers can extend the data models with additional calculated measures and dimensions. This enables the story creator to delve into further insights from their data. Let us start by creating a calculated dimension that divides the Store dimension in our data into Studios and Non-Studios.
- Click Gross Margin % for Actual Chart
-
Click Edit and Open Right Panel
-
Click + Add Dimension
- Scroll and Click + Create Calcluated Dimension...
ℹ️Welcome to Calculated Dimensions!
Calculated Dimensions are useful when you want to enrich your dimension if your model does not have the needed format.
You can choose to combine existing dimensions to create your own dimensions. There are two calculated dimensions that can be created:
Calculated Dimension: Use formulas to create new dimension members
Account Based Dimension: Use ranges within an existing measure to determine how to group dimension members together
🚩As we want to identify the stores that are studios, we will use the regular calculated dimension.
- Click Calculated DImension
🚩The formula field for Calculated Dimensions uses conditional logic and function formulas to create the Dimension rules. This offers the business analyst great flexibility in defining new calculated dimensions.
- Press Ctrl + Space on the Keyboard
- Click IF
🚩Our IF statement has three fields. The first field is used as a condition that evaluates to true or false. The second field is the Dimension value if True and the third field is the Dimension value if False.
- Click the Condition (First Field) in the IF Formula
- Press Ctrl + Space on the Keyboard
💡Using Ctrl + Space is a great way to learn how to use the Calculation Editor. Using this hotkey combination will always bring up all possible functions and measures/dimensions that can be typed into the according field for the ease of the user.
ℹ️Welcome to String Functions in the Calculation Editor! There are a variety of different String Functions that can be used to transform the Dimension values to a specific use case.
FINDINDEX: Search for a substring and return its 0 based index ENDSWITH: Returns True if given string ends with user given substring TRIM: Removes unwanted leading and trailing spaces REPLACE: Replace characters in a string with a specified replacement SPLIT: Returns substring from a string using a specified divider UPPERCASE/LOWERCASE: Convert a string to all Lower or all Upper case CONCAT: Combine two strings together LEFT/RIGHT: Returns the specified number of characters from the beginning or end of a string
Let us use RIGHT function to categorize Stores by if they are a Studio.
-
Scroll till RIGHT is Visible
-
Click RIGHT
🚩Here we are specifying the Dimension we are reading our string from
-
Type St in the First Input Field for RIGHT
-
Click Store
ℹ️Since Dimensions often have an ID and Description, it is important to clarify that we are looking to parse the Store name from description here.
-
Type in a Period "."
-
Press Ctrl + Space on the keyboard
-
Click Description
🚩We know we are trying to divide our Store dimension into Studios and Non-Studio. Since we are looking for "Studio" at the end of store name, we know we should filter on 6 characters using our RIGHT string function.
- Type "6" in the Second Input Field for the RIGHT Function
🚩Let us compare the last 6 letters of our store name with Studio to group them into two Store Groups.
-
Type in ="Studio" following the RIGHT formula
-
Type in "Studio" in the TRUE Field for the IF Function
-
Type in "Non-Studio" in the FALSE Field for the IF Function
🚩Format will parse your Formula and identify if there are any problems with the input parameters.
- Click Format to Validate the Formula
🚩Great! Our formula is valid and good for use in defining a new Calculated Dimension. Let us name this Dimension and use it in our charts.
-
Name the Calculated Dimension as Store Group
-
Click OK
🚩By creating a Calculated Dimension, we are able extract further insights from our data. We can now see that Gross Margin % is higher for Studios than other stores. This could be of interest to us for further financial analysis and investments.
🚩Let us use our new Calculated Dimension to find out the split of Average Sales Revenue by Store Group.
- Click Avg Sales Revenue for Actual Chart
- Click + Add Dimension
-
Scroll and Click Store Group
-
Click Inside the Builder Panel to Collapse the Dimension Selection Drop Down Menu
🚩We can now see that Avg Sales Revenue is also higher for Studio Stores. It could be a good business decision to change our contract structure with our Studio Stores!
Objective: Up to this point, we have created a lot of visualizations. For us to easily draw conclusions and highlight key insights, we should add variances and thresholds on our charts in our dashboard so we can draw attention to changes and outliers.
Estimated Time: 5 mins
Exercise Description: You as a Dashboard Designer will be able to create previuos period comparisons for being able to highlight the charts with critical points.
Key Features:
Define variances in our charts
🚩Please make a copy of the dashboard "Exercise 2.2 and 2.3 - Thresholds and Variances"
Goto EDIT and Open the right builder panel using the icon
🚩First, let us add a variance to our KPI for average sales revenue in stores so we can compare if average sales revenue has dropped this year relative to our previous year's performance. If you can recall, our KPIs currently have a 2021 time filter applied on them.
-
Click on the Avg Sales Revenue Chart
-
Click Add Variance as below or from Chart Add-Ons
- Click Add Version/Time and choose Order Date
🚩We want to compare our data in 2021 year to date with the calculation for the previous year. By comparing to previous period, SAC will automatically calculate this variance for us.
🚩After creating our variance, the absolute value may still be hard to contextually understand. We want to know the percentage difference so let us apply _Percentage Display Optio_n to this variance.
- From the builder panel under display options choose Percentage and uncheck numeric
Our chart is now enhanced to show variance display to previuos period.
- Click Done to close the Variance Edit panel.
- Close the Builder Panel Save your story
Objective: To be able to create thresholds based on ranges in our SAC story
Estimated Time: 5 mins
Exercise Description: You as a Dashboard Designer will be able to create Within Thresholds, you can create a threshold based on a number range or against another measure. In our case, we are interested in a number range.
Key Features:
- Define thresholds in our charts
🚩Please make a copy of the dashboard "Exercise 2.2 and 2.3 - Thresholds and Variances"
🚩Open Story in Edit mode
🚩We would like to create threshold for the Table as highlighted
- Click on the Right panel Icon to open the Builder panel
-
Click on the Account under the Columns and Select Thresholds -> New Threshold
- In the Threshold Panel Select Measure as Gross Margin %
-
We can now define the threshold as a Number Range for Gross Margin % .
-
Add the Value 30 for Gross Margin % >= scenario so that its indicated as green in the table cells.
-
Click Apply.
-
Save your Story and see that Gross Margin% is now shown in green for % >=30%
🚩Thresholds in the table are now color coded based on number range.
You have completed Thresholds, and Variances section!
You are now able to:
- Use recommended comparison to quickly add variances to a chart
- Add a threshold to a table
Continue to - Exercise 3 - Excercise 3