Introduction
Advanced chart calculations (advanced operations) are a new feature that allows users to apply additional calculations on measures used in a chart.
There are two types of advanced calculations:
Moving calculations- that return an accumulated result of several records,
Some use cases are:Accumulated ARR - show the ARR growth over time by the date a deal was closed.
Daily users Moving Avg - show your users a 7-day average for each date to remove the impact of weekdays and outliers.
Compare current revenue with the previous year's average - to understand your trend without the impact of seasonality and outliers.
Comparison- compare the current record to a different record.
Some use cases are:MoM Growth displays the percentage of growth between the current record and the previous record.
Same Quarter previous year comparison - see the absolute difference between the current quarter result and the same quarter in the previous year. This approach is best used when there is a strong seasonal impact on your activity.
Activating Advanced Calculations
Activating the advanced calculations option for a measure is easy and intuitive.
You can expand the measure you want to use in the Data tab of the visualization properties panel.
Click on the drop-down menu near the Advanced operation label and select the calculation method you want to use.
Advanced Calculation Types
There are two categories of advanced calculations:
Moving Aggregations - this category returns the cumulative result of a range of records.
Comparisons- this category returns a value calculated using the current and comparison record.
Optional advanced calculations:
Category | Type | Description | Formula |
Moving Aggregations | Running total | returns the cumulative result of all records up to the current record, including the current record |
|
| Moving Calculation | Returns the cumulative result of a set of records as defined by the user. |
|
Comparison | Value | Returns the value of the comparison record | [comparison record] |
| Difference | Returns the difference between the current record and the comparison record | [current record] - [comparison record] |
| Rate | Returns the rate between the current record and the comparison record | [current record] / [comparison record] |
| Growth | Returns the growth rate between the current record and the comparison record | ([current record] / [comparison record])-1 |
We'll use this data as a reference for the detailed explanation of each type of calculation.
These are the different types of advanced calculations:
Running Total
this option calculates the cumulative results of all the records up to the current record, including it.
A common use case is to show the ARR growth over time.
Select one of the four optional aggregations to define the measure.
This is the result of a running total calculation using the above table
Moving calculation
This option calculates the cumulative result of the set of records the user defines.
A common use case shows a moving average to reduce the impact of seasonality and outliers.
Aggregation - select one of the four options.
Rows before- set the start of the range of records to use in the calculation. negative values mean records before the current record, and positive values mean records after the current records.
Rows after- set the end of the range of records to use in the calculation.
Null if there are not enough values - when checked for records, they are close to the chart's start or end. a value wouldn't be displayed
(In the above example - a value will be displayed only from the seventh record onward)
Below is an example of each record's moving average of the previous two months and the current record.
Value
This option shows the value of the selected record instead of the current record's value.
A common use case compares the current Quarter to the same Quarter in the previous year.
The table below shows each value vs. the same Quarter's value in the previous year.
Difference
This option calculates the difference between the current record and the compared record using this formula:
current record - comparison record
The table below shows the difference between each quarter and the previous quarter.
Rate
This option calculates the rate between the current record and the compared record using this formula:
current record/comparison record
The table below shows the rate between each quarter and the previous quarter.
Growth
This option calculates the growth rate between the current record and the compared record using this formula:
current record/comparison record -1
The table below shows the growth rate between each quarter and the previous quarter.
Known Limitations
Currently, advanced calculations are only available in four visualizations:
Bar Chart
Combo Chart
Line Chart
Table