Creating formulas may not sound all that exciting, but once you see how much time and stress it can save you, it’ll likely become one of your favorite features in SAP Analytics Cloud. Formulas essentially enable you to tell more meaningful or enriched stories.
There are two ways to create formulas:
In this post, we’ll discuss both options and the benefits of each.
When you hear the term 'formula', you may think calculation. While this is true, it's much more than that. Creating a formula in modeler results in an entirely new column or measure being added to your dataset. These new measures can then be used in your stories just like any other measure such as sales revenue, units sold, gross margin, etc. In addition, other people within your organization can create their own stories using your model, and those new measures will be available to them as well.
The figure below shows a sample dataset that includes various measures. Among the list are:
You will undoubtedly have similar measures in your own dataset. However, there may be a time when you want to have other measures such as compound annual growth rate (CAGR), Year over Year (YOY), EBITDA, and so on.
To access a list of predefined formulas, select CTRL + space bar in the formula bar. Each formula includes a description so you can identify what they do.
Let’s look at a quick example of calculating revenue. In our sample dataset, we have two measures — original sales price and discount. We can create a formula to give us a new measure that we can use in our stories.
In modeler, we choose a blank row and entered "Revenue". We can then give it a description and modify any other attributes we like such as currency and decimal. Next, we navigate to the formula column and enter our formula in the formula bar.
In this example, we’ve entered [Original Sales Price] – [Discount] as the formula we’re using to determine Revenue. Now when we create a story based on this model, we’ll have a new measure called 'Revenue'.
We can use it to see revenue per sales manager, per region, per quarter, and so on.
The Advanced Formula Editor is similar to using the formula bar, but it gives you more options. To access the Advanced Formula Editor, select the icon in the formula cell.
On the right side of the Editor, you will see three types of Formula Functions:
In the image below, the Functions menu is expanded to show all the available functions.
You can begin typing, or press CTRL + space bar to see a menu of different formulas.
Once we save the model with the formulas, the resulting new measures will be available to use in our stories based on that model.
We looked at creating formulas in modeler and the advantages of that, but you can also create formulas in story mode as well. The advantage of creating formulas in story mode is that you may need to do an ad hoc calculation that you only plan to use one time, or maybe you just forgot to do it in modeler.
Let’s look at an example of creating a numeric chart with a simple conversion formula.
First, we’ll open our story and add a Numeric Point chart.
Next, we’ll select ‘+ Click to Create a New Calculation’.
We can then create our formula (or calculation) based on the measures we have available in the model.
In this case, we want to create a new formula that calculates the conversion of two measures:
So, the formula would be: [Website Customers] / [Website Visitors] x 100
We’ll give this formula a name — let’s call it “Sales Conversion”, and format it accordingly.
The result is a Numeric Point chart showing the conversion of web traffic.
There are other kinds of calculations you can make in the calculation editor:
You can also create Calculated Dimensions in story mode. When inserting a chart, select ‘+ Click to Create a New Calculation’.
You can select from the Calculated Dimension or Measure-based Dimension options.
Let’s explore Calculated Dimensions first. You may create a calculated dimension when you want to link dimensions together or group them, using the Formula Functions available in the screenshot above. For example, with a grouped dimension, you can choose the ‘IF’ function to create a member grouping for your data and group sales from specific cities by region.
Don’t forget – within the Calculation Editor ‘Edit Formula’ area, you can click CONTROL+space to see all available dimensions in the model or type specific values to filter the list dynamically to objects that contain those values.
Now, let's talk about creating Measure-based Dimensions. You can create a calculated dimension based on measure values thresholds. For example, you can look at Sales Quantity by each Sales Agent, and set value thresholds based on your criteria for what are high, medium, and low sales.
Here, the measure used in the calculation is Units Sold, and the dimension context is Sales Agent because we’re looking into the units sold by these individual sales agents. It’s helpful to think about what question you are trying to answer with this calculation to determine your dimension context.
Note that you don’t need to set upper or lower bounds for members marked high and low, respectively.
The result of this measure-based dimension calculation will give us a chart that shows units sold, by sales agent, bound to the criteria of high, medium, and low sales.
Formulas in tables work the same as they do in a chart — you can either add them at the model or story level.
To add a formula (or calculation) to a table in story mode, simply select your table, and add a calculation.
Add your formula as you would with a chart. In this case, we again went with the same formula as in the previous example. The result is a new column called ‘Revenue’ which is based on our formula: [Original Sales Price] – [Discount].
Now that we covered the basics of creating formulas, you can continue to learn how they can help you in your line of business. Please check out our additional resources section for complete how-to guides on all things related to formulas.
There are two ways to create formulas:
- Modeler
- Stories
In this post, we’ll discuss both options and the benefits of each.
Formulas in Modeler
When you hear the term 'formula', you may think calculation. While this is true, it's much more than that. Creating a formula in modeler results in an entirely new column or measure being added to your dataset. These new measures can then be used in your stories just like any other measure such as sales revenue, units sold, gross margin, etc. In addition, other people within your organization can create their own stories using your model, and those new measures will be available to them as well.
The figure below shows a sample dataset that includes various measures. Among the list are:
- Quantity sold
- Gross margin
- Discount
- Original Sales Price
- Price (fixed)
You will undoubtedly have similar measures in your own dataset. However, there may be a time when you want to have other measures such as compound annual growth rate (CAGR), Year over Year (YOY), EBITDA, and so on.
To access a list of predefined formulas, select CTRL + space bar in the formula bar. Each formula includes a description so you can identify what they do.
Let’s look at a quick example of calculating revenue. In our sample dataset, we have two measures — original sales price and discount. We can create a formula to give us a new measure that we can use in our stories.
In modeler, we choose a blank row and entered "Revenue". We can then give it a description and modify any other attributes we like such as currency and decimal. Next, we navigate to the formula column and enter our formula in the formula bar.
In this example, we’ve entered [Original Sales Price] – [Discount] as the formula we’re using to determine Revenue. Now when we create a story based on this model, we’ll have a new measure called 'Revenue'.
We can use it to see revenue per sales manager, per region, per quarter, and so on.
The Advanced Formula Editor
The Advanced Formula Editor is similar to using the formula bar, but it gives you more options. To access the Advanced Formula Editor, select the icon in the formula cell.
On the right side of the Editor, you will see three types of Formula Functions:
- Functions [IF, ABS, LOG, INT,…]
- Conditions [AND,OR, <,>,…]
- Operators [-,+,/,*]
In the image below, the Functions menu is expanded to show all the available functions.
You can begin typing, or press CTRL + space bar to see a menu of different formulas.
Once we save the model with the formulas, the resulting new measures will be available to use in our stories based on that model.
Formulas in Stories
We looked at creating formulas in modeler and the advantages of that, but you can also create formulas in story mode as well. The advantage of creating formulas in story mode is that you may need to do an ad hoc calculation that you only plan to use one time, or maybe you just forgot to do it in modeler.
Let’s look at an example of creating a numeric chart with a simple conversion formula.
First, we’ll open our story and add a Numeric Point chart.
Next, we’ll select ‘+ Click to Create a New Calculation’.
We can then create our formula (or calculation) based on the measures we have available in the model.
In this case, we want to create a new formula that calculates the conversion of two measures:
- Website Visitors — the amount of people who visit a website
- Website Customers — the amount of people who made a purchase from that website
So, the formula would be: [Website Customers] / [Website Visitors] x 100
We’ll give this formula a name — let’s call it “Sales Conversion”, and format it accordingly.
The result is a Numeric Point chart showing the conversion of web traffic.
There are other kinds of calculations you can make in the calculation editor:
- Calculated Measure — an operation that involves only measures. For example, we can create a new measure called ‘Net Price’ by creating a calculation: ‘Sales Price’ — ‘Discount’
- Restricted Measure — restricts the value of a measure to a specific dimension. For example, we can restrict our data to only show sales for California and label it “California Sales”. Now we have that measure for further use
- Difference From — creates a difference in measuring values between two dates. For example, the difference in Gross Profit between 2016 and 2017
- Aggregation — calculates different types of values such as average, sum, min, and max. For example, using aggregation type ‘average’, you could calculate the average sales of each manager per location. Once the new calculation is created, you can use the measure — ‘Average sales per manager’
- Date Difference — creates a calculation that shows the time interval between two dates. To use this, your model must have date columns or date (time) hierarches with day level granularity. For example, you can calculate the number of days between Product Order Date and Ship Date.
- Dimension to Measure — allows you to convert a string value (text) to a numeric value which can then be used to perform measure based calculations. For example, if we have dimension values with number part and a $ sign, we can remove $ from the dimension and convert the number part as a measure using this function.
You can also create Calculated Dimensions in story mode. When inserting a chart, select ‘+ Click to Create a New Calculation’.
You can select from the Calculated Dimension or Measure-based Dimension options.
Let’s explore Calculated Dimensions first. You may create a calculated dimension when you want to link dimensions together or group them, using the Formula Functions available in the screenshot above. For example, with a grouped dimension, you can choose the ‘IF’ function to create a member grouping for your data and group sales from specific cities by region.
Don’t forget – within the Calculation Editor ‘Edit Formula’ area, you can click CONTROL+space to see all available dimensions in the model or type specific values to filter the list dynamically to objects that contain those values.
Now, let's talk about creating Measure-based Dimensions. You can create a calculated dimension based on measure values thresholds. For example, you can look at Sales Quantity by each Sales Agent, and set value thresholds based on your criteria for what are high, medium, and low sales.
Here, the measure used in the calculation is Units Sold, and the dimension context is Sales Agent because we’re looking into the units sold by these individual sales agents. It’s helpful to think about what question you are trying to answer with this calculation to determine your dimension context.
Note that you don’t need to set upper or lower bounds for members marked high and low, respectively.
The result of this measure-based dimension calculation will give us a chart that shows units sold, by sales agent, bound to the criteria of high, medium, and low sales.
Formulas in Tables
Formulas in tables work the same as they do in a chart — you can either add them at the model or story level.
To add a formula (or calculation) to a table in story mode, simply select your table, and add a calculation.
Add your formula as you would with a chart. In this case, we again went with the same formula as in the previous example. The result is a new column called ‘Revenue’ which is based on our formula: [Original Sales Price] – [Discount].
Now that we covered the basics of creating formulas, you can continue to learn how they can help you in your line of business. Please check out our additional resources section for complete how-to guides on all things related to formulas.
- SAP Managed Tags:
1 Comment
