On the road with BusinessObjects Cloud: calculations best practice. Formulas put upside down
My journey in Cloud Analytics led me to discovering the formula and calculation possibilities some more. This journey-page also has a link to all blogs I wrote on SAP BusinessObjects Cloud. I most of them we cover calculations as part of visualizations (so called calculated measures). In this article we gonna touch the formula editor as part of the BusinessObjects Cloud modeler. As usual I added a demo movie to this post so you can review things to your needs. The data I used is a very simple "home-made" Excel composing of sales numbers per product group per month. It spans 4 years. Here you can download my Excel and try for yourself. If you want please extend my excel with some more metrics so also practice with IF ... THEN .. ELSE statements; I skip that piece here.
Let's have a look:
Working with calculations and formulas can be done in either two sections of SAP BusinessObjects Cloud: on the storyboard section as part of the data visualization. Here 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 article I primarily focus on the this formula editor. One 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 below 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 below result: the CAGR 2016 for example is calculated as CAGR([Sales];;)
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, dividing this total by the number of time periods defined. It does this in a rolling format, meaning I could uses this formula to create for example a rolling last 3 months average. Highly powerful and for me a delight to use. The formula I used to do so is SMA([sales];[month];)
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)).
here is the video: