N - Designer Mode - Formulas

Formulas

a - Introduction

From the moment you create an indicator, a formula is created by default (most often a sum or a separate value account), but it is sometimes necessary to modify formulas in order to obtain the desired indicator, such as average basket, average price, sales index, etc.

To create or modify a formula, you must enter the advanced designer of the widget.

In case you create a new indicator, click add, and then once in the data explorer, click on the to the left of the search bar. You will then see a new window with a formula editor at the top, and below the data and function explorer.

All you have to do is look for the field you want to calculate in the search bar.

By hovering over the field of your choice, a menu "More..." will appear and allow you to choose the appropriate aggregation (sum, count all, min, max...) for your indicator. The default aggregation appears to the right of the field name.

Let's say you clicked sum and it was an error. You can then either delete the formula in the editor and then reselect the correct aggregation or click on the formula, then on "Type(Sum)" and choose the new aggregation.

In case the indicator is already created, click on the pencil and the formula editor will appear.

b - Quick functions

It is possible to quickly change a simple indicator (a measurement) using the quick functions. You will find them in the menu of the measure.

The main quick functions are: 

• • Calculationof the contribution, 
  • Previous value (last year, last quarter, last month, last day)
  • The evolution over time (in value) (growth rate, since the previous period (day, week, month), since the same period quarter/month/week before),
  • % change over time (from same period, previous year/quarter/month/week
  • Sum or cumulative average, since the beginning of the year, quarter, month, week or category.

By clicking on one of them, you will see that the formula has changed in the editor (Quick function >% evolution over time >  Since the same period, previous month):

You can find all these functions in the "Functions" tab of the formula editor, with their definition and user manual.

c - Filtered formulas

Sometimes some formulas must take into account specific criteria. It is then possible to create so-called filtered measures. 

The metric is then filtered on a given filter level, regardless of the filters in the dashboard or widget. The filterapplied to a measure is constructed in the same way as a dashboard or widget filter.  

The formula is of the type:  (measure, filter1, filter2, etc.)

Let's imagine that we want to have an indicator that always indicates the Benelux turnover, regardless of the country filter of the dashboard. The formula is then:

([Total MtRetailSalesHT], [COUNTRY])

Where [COUNTRY] was filtered on Belgium, Netherlands and Luxembourg, regardless of the country filter set in the dashboard. 

This indicator will continue to be filtered on other fields (e.g. product family, subfamily, dates), but the Country  filter at the dashboard level will no longer modify this indicator.

A very important function for filters applied to a measure is the ALL() function. It then includes all the members of a field, regardless of the filter applied on this field at the dashboard level on this field. 

Now let's imagine that we want to know the contribution of the Benelux CA to the global CA. For this, we will have the following formula: 

([Total MtRetailSalesHT], [COUNTRY]) / ([Total MtRetailSalesHT], ALL([COUNTRY]))

Where ([Total MtVenteRetailHT], [COUNTRY]) is filtered on Benelux, and ([Total MtVenteRetailHT], ALL([COUNTRY])) takes into account all countries.

d - "Grouped" aggregations

Simple aggregations have already been discussed a little above, these are functions such as SUM(), AVG(), MAX(), COUNT(), etc. 

An example of a simple aggregation is a manager who wants to know the average turnover generated per operation for each seller.

An example of a more complex aggregation is a manager who wants to know the average daily turnover generated for each salesperson.

In this case, the multipass aggregation will first calculate a sum per day and then calculate the average of the sums obtained.

Example:

Results: Example 1, average turnover per seller: 

AVG([MtRetailSalesHT])

John: (120+110+80+70+60+90)/6 = 88.3

Clear: (120+130+60+80)/4=97.5

Results : Example 2, average daily turnover per seller:

AVG([Days In Date]),SUM([MtVenteRetailHT]))

John: ((120+110+80)+(70+60+90))/2 = 265 

Clear: ((120+130)+(60+80))/2=195 

This would then allow us to obtain a pivot table of this type:

e - Save a formula

It is possible to save a formula so that you can use it again later. 

To do this, after creating the formula, click on the star at the top right of the formula editor window. Rename the formula as you wish and validate your choice.

You will now find this formula at the beginning of the data explorer under the name you gave it.