Introduction

Advanced chart calculations allow users to perform powerful calculations on chart measures — including growth rates, moving averages, accumulated values, rankings, and totals — through a simple, intuitive no-code UI.

Check this data app for detailed use cases and a live example: https://www.astrato.io/demo-apps/advanced-calculations

Advanced Calculation Types

There are several categories of advanced calculations:

Moving Aggregations — returns the cumulative result of a range of records (Running Total, Moving Calculation, Running Rate).
Period over Period — returns a value calculated using the current and a comparison record (Value, Difference, Rate, Growth, Correlation, Accumulated Growth, Annualized Period Growth, CAGR).
Other — Rank, Total, Compare to Total.

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.
	Running Rate	Returns the cumulative rate of all records up to and including the current record , the rate is calculated by dividing the cumulative value by the total value of the measure
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
	Correlation	Returns the correlation of two records from different periods
	Accumulated Growth	Returns the growth rate by comparing the running total of two measures	running total(measure) / running total(reference) -1
	Annulized Period Growth	Returns the growth rate between periods in annualized value. n- represents the number of periods in a year.	power(([current record] / [comparison record]),n) -1
	CAGR- compound annual growth rate	Returns the compound growth rate over multiple periods. n- represents the number of years from the previous period.	power(([current record] / [comparison record],-n),1/n) -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. There are different options of comparison.	measure/ sum_total(measure) measure - avg_total(measure)

Activating Advanced Calculations

Expand the measure you want to use in the Data tab of the visualization properties panel. Click the drop-down next to Advanced operation and select the calculation method.

Period over Period

Value
- Shows the value of the selected comparison record instead of the current record's value. A common use case: compare the current Quarter to the same Quarter in the previous year.
Difference
- Calculates the difference between the current and compared records: current record − comparison record
Rate
- Calculates the rate between the current and compared records: current record/comparison record
Growth
- Calculates the growth rate: current record/comparison record − 1
Correlation
- Calculates the correlation between a period-over-period measure of two dimensions. Primarily used in a heatmap or correlation matrix.
- - Additional dimensions — dimensions used to calculate the correlation, typically including a date or timestamp field.
  - Compare with the period — specifies how many records to compare back.
  - Comparison type — Absolute (difference) or Percentage (rate change).
Accumulated Growth
- Calculates the growth rate between the running totals of two measures: running total(measure) / running total(reference) − 1
Annualized Period Growth
- Calculates the annualized growth rate between two periods. Useful for comparing QoQ or MoM growth to expected YoY growth.
- power(Current Period / Previous Period, n) − 1 where n = number of periods in a year.
CAGR — Compound Annual Growth Rate
- Calculates the average growth rate over multiple periods. Typically used for average return on investment over multiple years.
- power([current record] / [comparison record], 1/n) − 1 where n = number of periods back.

Nested Time Dimension mode
A nested time dimension is needed when you try to calculate a period-over-period measure, but you don't want to display the time dimension.
For example, displaying YoY value in a KPI, or showing YoY value for countries in a bar chart.
- Supported Period-over-Period Calculations in nested mode:
  - Value
  - Difference
  - Rate
  - Growth
  - CAGR - Compounded Annual Growth Rate
  - Annualized Period Growth
- Supported Measure
  Two types of measures are supported in the nested time dimension mode.
  The measure must include a time filter.
  - Predefined Time period filter
    Measures that include a predefined time period filter.
    For example, the measure below.
    When a predefined filter is used, the offset unit in the advanced calculation is automatically set based on the period.
    In the above example, the offset unit will be Year.
  - Parameterized Period Filter
    A measure that includes a time period filter that is defined using parameters.
    For example, the measure below is filtered to a period that end users can set.
    When this type of measure is used, the offset unit in the advanced calculation is defined manually by the user.
- Defining the Advanced Calculation in the Visualization
  In the advanced calculation, when selecting a period-over-period calculation.
  Activate the Nested time dimension switch.
  If you use a parameterized period filter, select the offset time period.
  The number of units for the offset is defined in the comparison with period control.

Moving Aggregations

These are the different types of moving 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.
Running Rate
An everyday use case shows the cumulative churn rate and identifies the types of customers who are less likely to churn.
This chart shows the cumulative rate of deals closed by the time it took to close them; it helps identify the types of deals that are easier and faster to close.
- Select one of the four optional aggregations to define the measure.
- If the chart has multiple dimensions, select the dimension for the running total.
- Order By - when multiple running totals are in a table, you can order them by different measures or dimensions.
Below is an example of a chart comparing revenue progress across months and years.
We can learn from the chart, for example, that only in 2024 did we got to 50% of the income in the first half of the year

Other Functions

Rank
Returns the rank of a measure by the defined dimension.
- Order — defines how to sort the measure when ranking.
- Dense — standard rank or dense rank.
- Dimensions — the dimensions the ranking is scoped to. By default, ranking is across all rows.

Total
- Returns a measure's total or sub-total value by the defined dimension combinations.
- Aggregation — Sum, Min, Max, Avg, Percentile, Control Value.
  Control value- returns the value of the Avg Plus or Minus a number of Standard Deviations
- Dimensions — dimensions the aggregation is scoped to. By default, across all rows.

Compare to Total
- Compares each measure value to a total value. Use cases: share of total, deviation from average, growth vs average.
- Type of comparison:
  - Rate — measure / total_sum(measure)
  - Difference — measure − total_avg(measure)
  - Growth — measure / total_avg(measure) − 1
- Aggregation — Sum, Min, Max, Avg, Percentile, or Outliers.
  - Outliers- returns one if a value is an outlier when compared to the Avg value, plus a number of defined standard deviations
- Dimensions — the dimensions the aggregation is scoped to.