# On the road with BusinessObjects Cloud: calculations best practice. Formulas put upside down

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: