SAP

Author

Ewan Maalerud

Ewan Maalerud

Ewan is part of the Digital Customer Experience team for SAP Analytics Cloud, based in Vancouver, Canada. He’s passionate about data analytics and communicating the value of using data to optimize business outcomes.

Keep in touch

Subscribe for the latest news, updates, tips and more delivered right to your inbox.

Subscribe to updates

Category

Learning

Connect with us

using formulas in sap analytics cloud

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:

  • 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)

Selecting measures SAP Analytics Cloud

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.

Formulas in modeler SAP Analytics Cloud

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’.

Formulas in Stories SAP Analytics Cloud

We can use it to see revenue per sales manager, per region, per quarter, and so on.

formulas in stories SAP Analytics Cloud

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.

Advanced Formula Editor SAP Analytics Cloud

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.

Advanced Formula Editor SAP Analytics Cloud

You can begin typing, or press CTRL + space bar to see a menu of different formulas.

Advanced Formula Editor SAP Analytics Cloud

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.

numeric point SAP Analytics Cloud

Next, we’ll select ‘+ Click to Create a New Calculation’.

calculation story SAP Analytics Cloud

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.

formatting SAP Analytics Cloud

The result is a Numeric Point chart showing the conversion of web traffic.

conversion in sap analytics cloud

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. 

Calculation Editor SAP Analytics Cloud

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 calculation SAP Analytics Cloud

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.

Additional Resources

SAP Analytics Cloud earns a top ranking from BARC

See how SAP Analytics Cloud performed in the world’s largest survey of Business Intelligence software users.