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.
Rank
Nested Calculations
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. |
|
Period over Period | 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 |
| Accumulated Growth | Returns the growth rate by comparing the running total of two measures | running total(measure) / running total(reference) -1 |
Others | Rank | Returns the rank of a measure by the selected dimensions | Rank(measure) |
| Total | returns the total value of a measure by the selected dimension | Sum(measure) |
| Compare to total | returns the result of a measure vs the total result of a measure. | measure/ sum_total(measure)
measure - avg_total(measure) |
Nested Calculations | Nested Calculation | Returns the aggregate result of a measure after calculating an internal virtual table using an additional dimension just for this measure. | avg(measure splited by dimension) |
| Nested Ratio | Returns the ratio of the measure aggregated on two different internal levels | avg(measure split by dimension 1) / max(measure splited by dimension 2 |
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.
If there are multiple dimensions in the chart, select the dimension to use for the running total.
Order By - when there are multiple running totals in a table, you can order them by different measures or dimensions
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.
Period over Period
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.
Accumulated Growth
This option calculates the growth rate between the running total of the measure and the running total of a reference measure using this formula:
running total(measure)/running total(reference measure) -1
The table below shows the running totals for the current and previous years in the right free column, and the accumulated growth result is shown in the rightmost column.
Rank
This option returns the rank of a measure by the defined dimension.
The settings for rank are:
Order- defines how to sort the measure when performing the ranking.
Dense- defines the type of ranking, rank, or dense rank.
the difference is illustrated in the table below:
Dimensions- define the dimensions that the ranking is done by.
By default, the ranking will be done across all the rows in the table.
The table below shows the Rank columns using the revenue value; one is total with no dimension defined, and one with country as the dimension for ranking.
Total
This option returns a measure's total\ sub-total value by the defined dimensions combinations.
The settings for the total option are:
Aggregation- select one of the options: Sum, Min, Max, Avg
Dimensions- define the dimensions by which the aggregation is done.
By default, the aggregation will be done across all the rows in the table.
The Table below shows each country's total and average revenue per occupation.
Compare to Total
This option allows comparison to total in different ways, use cases for this option can be
Compare each value to the average value, compare each value to the max value, or calculate the share of the total.
The settings for comparison to the total are:
Type of comparison- there are three different types of comparison.
Rate- divide the measure by the total result, for example, the share of the total.
measure\ total_sum(measure)Difference- subtract the total result from the measure; for example, compare the measure to the average result.
measure - total_avg(measure)Growth- divide the measure by the total and substruct 1, for example, show the growth vs the average result
measure/total_avg(measure)- 1
Aggregation- select one of the options: Sum, Min, Max, Avg
Dimensions- define the dimensions by which the aggregation is done.
By default, the aggregation will be done across all the rows in the table.
The Table below shows each occupation's share of the country's revenue and average revenue per occupation and difference and growth vs the country's average.
Nested Calculation
This option allows for adding a dimension specifically for the measure.
This allows, for example, to show the monthly revenue for each country and occupation.
These are the settings for nested calculation:
Aggregation- the type of external aggregation to use
Additional dimensions- the list of internal dimensions to include when calculating the measure.
The table below shows the average monthly revenue in each row, although the month isn't a dimension in the table.
Nested Ratio
This option allows for adding dimensions specifically for the measure.
This allows, for example, the stickiness rate of a SAAS product
by calculating the ratio between average daily active users and monthly active users.
The settings for this option are:
Aggregation- the type of external aggregation used in the numerator expression.
Bottom Aggregation- the type of aggregation to use in the divider expression.
Additional dimensions- the internal dimensions to add to the numerator expression.
Bottom dimensions- are the internal dimensions to be added to the divider expression.
Known Limitations
Currently, advanced calculations are only available in these visualizations:
Bar Chart
Combo Chart
Line Chart
Table
KPI
Dot Plot
Scatter Chart