Part Four: On the Road with SAP BusinessObjects Cloud—Calculations Best Practices

My journey into cloud analytics led me to uncover the formula and calculation possibilities.  In most of the other blogs in this series, I covered calculations as part of visualizations (so called calculated measures). In this article, I’m going to touch on the formula editor as part of the SAP BusinessObjects Cloud modeler. (As usual, I’ve added a demo movie to the end of this post so you can review everything to your needs.)

The data I used is a very simple “home-made” Microsoft Excel spreadsheet composing of sales numbers per product group per month. It spans 4 years. You can download my spreadsheet and try for yourself. (If you want, you can extend my spreadhseet with some more metrics to practice with IF … THEN .. ELSE statements.)

Two Ways to Work with Calculations

Working with calculations and formulas can be done in two sections of SAP BusinessObjects Cloud. In the storyboard section as part of the data visualization, you can create calculated measures, Difference From measures or Restricted measures. The other section is from within the modeler using its powerful formula editor. In this blog, I’m primarily focusing on the formula editor. Once you have uploaded your data or connected to your data source, and have saved your model, the accounts-tab is the place to be.

Year over Year (YOY)

YoY is a highly useful function you can use in the formula editor as YoY. It returns the percentage of the difference between the value of a member in the current year compared to the previous year. My formula is YOY(Sales) and in the following graph I added the sales actual as a line to compare.

Compound Average Growth Rate (CAGR)

The CAGR function is more specific than YoY allowing you to specify the exact years to compare. To calculate compound annual growth rate, divide the value of an investment at the end of the period in question by its value at the beginning of that period, raise the result to the power of one divided by the period length, and subtract one from the subsequent result:

I created various CAGR formula’s for the following result. The CAGR 2016, for example, is calculated as CAGR([Sales];[201];[2016])

Simple Moving Average (SMA)

I was quite impressed by the power of the SMA function that is calculated by adding the value of a member for a number, and dividing this total by the number of time periods defined. It does this in a rolling format, meaning I could use this formula to create a rolling last 3 months average, for example. Highly powerful and for me, a delight to use. The formula I used to do so is SMA([sales];[month];[3])

SMA also works on the levels of Year and Quarter.

“Lookup” and “Restrict”

 The functionality of the “Restrict” and “Lookup” formulas are very similar: both formulas refer to an account, apply a point of view (POV) filter to a named dimension, and return an aggregated value. However, they differ in the way of visualizing the data in case the formula is based upon a member that has an embedded hierarchy. “Restrict” shows no values where no data has been selected.

“Lookup” simply shows the aggregated values for all lines. What I like about both is that the functions allow to have nested conditions. RESTRICT([Purchase],[d/Product]=(“Beans”, “Apples”) and [d/Date]=(“2016″,”2015”)) like I used for example, or even RESTRICT([Sales],[d/Date]=Previous(“Year”,1).Next(“Quarter”,5)).

For a detailed look, here’s the tutorial video.

For More Information

• Read Parts One, Two, and Three of the series, On the Road with SAP BusinessObjects Cloud.
• Learn more about SAP BusinessObjects Cloud.

This post originally appeared on Iver van de Zand’s blog and has been republished with permission.