In addition to standard time transformations such as % change or moving average, you can also apply custom time calculations to charts in the Dashboard Builder. For example, you can calculate arithmetic average, minimum or maximum over a period of time. This article explains how to create calculations involving time.
To add a time calculation, edit a chart and click a cogwheel icon in the Dimension FIlter tab.
In the Time dimension, select "Individual members" mode and click "Add calculation".
To create a calculation, follow the below four steps:
- Choose a function (AvgA, AvgG, Max, Min, Sum).
- Add the first argument to the function by clicking an element in the list of time periods.
- Click a box corresponding to the second argument and select a time period for it.
- Save the calculation.
There are the following functions available for calculations involving time:
- AvgA - returns simple arithmetic mean.
- AvgG - returns geometric mean.
- Max - finds maximum value in a specified time range.
- Min - finds minimum value in a specified time range.
- Sum - sums up all values within a given time range.
- IfNull, Const andd other functions which are described in this article.
All these functions assume two arguments defining starting and ending dates of the time range over which the function is applied.
You can also combine the above functions with arithmetic operators such as plus and minus to arrive at a more complex calculation. For example, you can create an index where an average for 1960 is set to 100, using the following formula:
In this formula, [X] is a placeholder that will be replaced with choosen elements as you save the calculation.
Note: a chart or a table that uses time calculations will not be automatically updated with new time periods if they appear in the dataset because you use "Individual members" mode which does not support auto update.