Introduction
Nested Calculations are a type of Advanced Chart Calculation in Astrato that allows you to apply an aggregation using an internal dimension, independently from the chart's other dimensions. This enables powerful analytics like stickiness rates, nested correlations, and dimension-specific aggregations.
Check this data app for detailed use cases and a live example.
Activating Nested Calculations
Activating the nested 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 Nested Calculation label and select the calculation method you want to use.
Then enter the required inputs based on the selected type of nested calculation.
These are the different types.
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 |
Nested Correlation | Returns the correlation coefficient for two dimensions or two measures | Corr(Value1,Value2) |
UDF | Using a User Defined Function from the data source, it returns the value defined in the UDF |
|
* UDF- is in private preview
Nested Calculation Types
Nested Calculation
This option allows for adding a dimension specifically for the measure.
This allows, for example, the presentation of the monthly revenue for each country and occupation.
These are the settings for nested calculation:
Aggregation- select one of the options: Sum, Min, Max, Avg, or Percentile.
When selecting a percentile, there are two additional settings:
Percentile value- Which percentile result should be calculated for example:
0.5 will return the median value.Continuous- when disabled, Astrato uses the percentile_disc function that returns the dataset value equal to or greater than the defined percentile.
When enabled, Astrato uses the percentile_cont function that calculates the percentile value based on the values in the dataset; the result is usually a value that isn't in the dataset.Approx- When enabled, the approx_percentile function is used. (currently available only in Snowflake and Databricks connections)
Additional dimensions- the list of internal dimensions to include when calculating the measure.
Output type- the type of output that is returned by the function
Default- returns the value of the calculation.
Dimension value- returns the value of the internal dimension value based on the measure value; it can return either the first or last value.
Value in a series- returns a calculation of the value of the last or first record in the series.
position- last or first record in the series
Function-
percent rank- returns the percent rank of the selected value.
Standard score- returns the number of standard deviations of the selected value.
Nested Ratio
This option allows for adding dimensions specifically for the measure.
This allows, for example, the calculation of the stickiness rate of a SaaS product by computing the ratio of average daily active users to monthly active users.
Settings:
Aggregation — the type of external aggregation used in the numerator expression.
Bottom Aggregation — the type of aggregation used in the denominator expression.
Additional dimensions — the internal dimensions to add to the numerator expression.
Bottom dimensions — the internal dimensions to add to the denominator expression.
Nested Correlation
This option returns the correlation coefficient between an independent and dependent variable. There are two types of correlation supported:
Correlation between two dimensions using one measure — a typical example is a correlation matrix.
Correlation between two measures using one dimension — a typical example is the correlation of two measures over time.
Settings for nested correlation:
Additional Dimension — the dimension used to create pairs of values from the independent and dependent variables. Typically a date dimension, as correlation is tested over time.
Compare measure — a measure that will be the dependent variable when correlating two measures. Use this setting only when the chart has one dimension.
User Defined Function (UDF) - Private Preview
User-defined functions allow the use of a custom function defined in the data source.
In the nested control, a UDF needs to have two inputs: an array and a control (number or string) and to reurtn a single value.
Below is an example of a UDF that calculates a percentile in the same way as in Excel.
CREATE OR REPLACE FUNCTION DEMO_DATASET.PUBLIC.PERCENTILE_EXCEL("DATA" ARRAY, "Q" FLOAT)
RETURNS FLOAT
LANGUAGE SQL
AS '
CASE
WHEN ARRAY_SIZE(data) = 0 THEN NULL
WHEN q * (ARRAY_SIZE(data) + 1) - 1 < 0
OR FLOOR(q * (ARRAY_SIZE(data) + 1) - 1) >= ARRAY_SIZE(data) - 1 THEN NULL
ELSE
ARRAY_SORT(data)[FLOOR(q * (ARRAY_SIZE(data) + 1) - 1)::INT] +
(ARRAY_SORT(data)[FLOOR(q * (ARRAY_SIZE(data) + 1) - 1)::INT + 1] -
ARRAY_SORT(data)[FLOOR(q * (ARRAY_SIZE(data) + 1) - 1)::INT]) *
(q * (ARRAY_SIZE(data) + 1) - 1 - FLOOR(q * (ARRAY_SIZE(data) + 1) - 1))
END
';Once the function is registered in the Astrato Semantic Layer, users can use it in the chart.
These are the settings for UDF calculation:
Select UDF- Select the UDF function to use
Additional dimensions- the list of internal dimensions to include when calculating the measure.
Other input- define the other input for the UDF
Output type- the type of output that is returned by the function
Default- returns the value of the calculation.
Dimension value- returns the value of the internal dimension value based on the measure value; it can return either the first or last value.
Value in a series- returns a calculation of the value of the last or first record in the series.
position- last or first record in the series
Function-
percent rank- returns the percent rank of the selected value.
Standard score- returns the number of standard deviations of the selected value.
Known Limitations
Pivot Table supports nested calculation in these combinations:
1 row dimension and 1 column dimension
Multiple row dimensions and no column dimensions
Nested Calculation isn't supported in these charts:
Pie chart
Sankey chart
Treemap chart
Word cloud
Gauge chart
Map