Skip to main content

groupByVarianceTable

This function calculates the variance between the sum-aggregated values for two measures (base and comparison), then breaks the variance down by one or more grouping dimensions.

Tip

If you want the statistical variation between a set of numbers, see the variance function.

You could use this function to compare Sales against Sales Target, broken down by Country and then further broken down by City. Each breakdown variance is classified as either a driver (a variance in the same direction as its parent) or an offset (a variance in the opposite direction). Therefore, if total sales fell short of the sales target, France would be a driver if total sales in France also fell short of the target; otherwise, France would be an offset. If more than one dimension is given, each child variance (driver or offset) is in turn broken by the next dimension — e.g. each Country variance broken down by City.

The function returns an ATL object with fields such as the base measure value (e.g. Sales), the comparison measure value (e.g. Sales Target), the variation amount, the variation as a percentage, and the drivers and offsets. The drivers and offsets fields each hold a list of breakdown variance objects for the next dimension (including the same fields). For further detail about the fields, see Result object fields. For guidance on extracting values from the object for use in your narrative, refer to Examples.

There are two main scenarios for variance, and this function covers both. First, you might want to compare two measures from the same table — e.g. Sales and Sales Target. Alternatively, you might want to compare the same measure from two sets of data — e.g. Sales for Q2 against Sales for Q1. In the latter scenario, you should use the filterRows function to create two datasets — e.g. one for Q1 data and another for Q2 data. Both approaches are demonstrated below.

Occasionally, you might not have figures for one of the measures you are comparing. For example, you might not have the Q1 sales data for Lyon to compare with the Q2 sales data for Lyon. In this case, the measure value is zero and the variance object does not appear in the list of drivers or offsets; instead, it goes into either the zeroBaseEntries or zeroComparisonEntries list. This is because you may want to describe this variance object differently in your narrative — e.g. "The Lyon branch was not open in Q1, so no comparison is possible".

There is a link to a downloadable example project in the Examples section.

Note

Available in "Describe the Table" and "Describe Row in Context" projects only.

Parameters

  • BASE DATA (table region)

    The first set of input data. Can be the whole table or a filtered table region.

  • COMPARISON DATA (table region)

    The second set of input data. Can be the whole table or a filtered table region. If comparing two different measures (e.g. Sales vs. Target), use the same dataset as that given in the BASE DATA parameter.

  • BASE MEASURE (column)

    The base measure is the column to sum and compare to the comparison measure. The column must contain numbers only. Each empty value is counted as a zero.

  • COMPARISON MEASURE (column)

    The comparison measure is the column to sum and compare to the base measure. This column must contain numbers only. Each empty value is counted as a zero.

  • DIMENSION (column or list)

    One or more dimensions by which to break down the total. For multiple dimensions, input a list of column variables. When you give multiple dimensions, the function works through the list from left to right.

  • DIRECTION PERCENTAGE THRESHOLD (number)

    When the absolute variance percentage is below this value, the variance direction is returned as NOCHANGE. This does not affect whether a child variance is regarded as a driver of offset of its parent.

Result object fields

The function returns a multi-level ATL object containing the following fields:

Field

Description

dimensionClass

The name of the dimension (e.g. Country or City). For the parent variance object, this is an empty string.

dimensionInstance

The value of the dimension (e.g. France or Germany if the dimension is Country). For the parent variance object, this is an empty string.

level

The top object is level 1. Its drivers and offsets, the variances broken down by the first dimension (e.g. Country), are level 2. Their drivers and offsets, which are the variances broken down by the second dimension (e.g. City) are level 3, and so on.

baseValue

The aggregated total for the base measure.

comparisonValue

The aggregated total for the comparison measure.

varianceValue

The variance value = (baseValue – comparisonValue).

variancePercentage

The variance value as a percentage of the comparison value. When the comparison value is zero, the variance percentage value is set to 100%.

baseMeasure

The name of the input base measure (e.g. Sales).

comparisonMeasure

The name of the input comparison measure (e.g. Sales Target).

drivers

A list of variance objects broken down by the next dimension, where the movement (variance) is in the same direction (positive or negative) to its parent variance. The list is given in descending order of absolute variance amount. This is an empty list for the last dimension (bottom level).

offsets

A list of variance objects broken down by the next dimension, where the movement (variance) is in the opposite direction (positive or negative) to its parent variance. The list is given in descending order of absolute variance amount. This is an empty list for the last dimension (bottom level).

zeroBaseEntries

A list of objects for a variance broken down by the next dimension, where the base measure value is zero or empty, and the comparison measure value is non-zero.

zeroComparisonEntries

A list of objects for a variance broken down by the next dimension, where the comparison measure value is zero.

direction

The variance direction. Either INCREASE, DECREASE, or NOCHANGE.

Examples

Assume a "Describe the Table" project with this data:

 

Code

Quarter

Country

City

Sales

SalesTarget

Profit

Row 1

1001

Q1

France

Paris

423,248.15

400,000

38,092.33

Row 2

2001

Q1

France

Paris

342,263.44

350,000

25,669.76

Row 3

3001

Q1

France

Lyon

215,863.83

250,000

19,427.74

Row 4

4001

Q1

France

Strasbourg

328,174.47

350,000

34,999.19

Row 5

1002

Q2

France

Paris

422,246.33

400,000

37,057.89

Row 6

2002

Q2

France

Paris

331,782.65

350,000

20,296.45

Row 7

3002

Q2

France

Lyon

235,183.78

250,000

22,545.78

Row 8

4002

Q2

France

Strasbourg

346,166.18

350,000

38,112.34

Row 9

5001

Q1

Germany

Berlin

377,242.65

350,000

33,951.83

Row 10

6001

Q1

Germany

Munich

291,370.72

200,000

23,309.65

Row 11

7001

Q1

Germany

Hamburg

290,455.48

300,000

32,531.01

Row 12

5002

Q2

Germany

Berlin

369,248.72

350,000

30,017.89

Row 13

6002

Q2

Germany

Munich

297,370.72

200,000

27,318.25

Row 14

7002

Q2

Germany

Hamburg

275,853.45

300,000

26,928.36

Example 1 — Two measures from the same table

This example shows how to calculate variance using two measures (columns) from the same table.

The following ATL uses the Sales column as the base measure and the SalesTarget column as the comparison measure. The grouping dimensions are Country and City. The direction percentage threshold is 1%. 

[[groupByVarianceTable(WholeTable, WholeTable, Sales, SalesTarget, (Country,City), 1)]]

The resulting ATL object can be seen in THIS DOWNLOADABLE RESULTS EXAMPLE.

Note

For reasons of space, we have not included the full result. We advise that you download the file using the link above and open it in a text and source code editor such as Notepad ++. This will make it easier to follow the rest of the example.

Printing the ATL object (while developing your narrative) produces a large output that is difficult to read. Therefore, we recommend you just print the part(s) you are checking at that moment. See Printing parts of the result for tips.

You can extract key data insights from the ATL object using dot and/or bracket notation. This is much easier if you first convert the function call into a global script variable, as shown below:

[[global.results = groupByVarianceTable(WholeTable, WholeTable, Sales, SalesTarget, (Country,City), 1);'']]

Now you can use dot/bracket notation (and other ATL functions) on the results variable to extract key data insights for your narrative:

[[global.results = groupByVarianceTable(WholeTable, WholeTable, Sales, SalesTarget, (Country,City), 1);'']]

The combined sales target for this period was [[currencyFormat(results.comparisonValue,'EUR')]]. Total sales were [[currencyFormat(results.baseValue,'EUR')]], which exceeded the target by [[abs(results.variancePercentage)]]%. This was driven by sales in [[map(results.drivers, x -> joinStrings(x.dimensionInstance," (",abs(x.variancePercentage),"% over target)"))]], and offset by sales in [[map(results.offsets, x -> joinStrings(x.dimensionInstance," (",abs(x.variancePercentage),"% below target)"))]].

Total sales in [[results.drivers[0].dimensionInstance]] were [[currencyFormat(results.drivers[0].baseValue,'EUR')]], which exceeded the country target ([[currencyFormat(results.drivers[0].comparisonValue,'EUR')]]) by [[abs(results.drivers[0].variancePercentage)]]%. This was driven by sales in [[map(results.drivers[0].drivers, x -> joinStrings(x.dimensionInstance," (",abs(x.variancePercentage),"% above target)"))]], and offset by sales in [[map(results.drivers[0].offsets, x -> joinStrings(x.dimensionInstance," (",abs(x.variancePercentage),"% below target)"))]].

Total sales in [[results.offsets[0].dimensionInstance]] were [[currencyFormat(results.offsets[0].baseValue,'EUR')]], which fell short of the country target ([[currencyFormat(results.offsets[0].comparisonValue,'EUR')]]) by [[abs(results.offsets[0].variancePercentage)]]%. This was driven by sales in [[map(results.offsets[0].drivers, x -> joinStrings(x.dimensionInstance," (",abs(x.variancePercentage),"% below target)"))]], and offset by sales in [[map(results.offsets[0].offsets, x -> joinStrings(x.dimensionInstance," (",abs(x.variancePercentage),"% above target)"))]].

The text output is:

The combined sales target for this period was €4,400,000. Total sales were €4,546,471, which exceeded the target by 3.33%. This was driven by sales in Germany (11.86% over target), and offset by sales in France (2.04% below target).

Total sales in Germany were €1,901,542, which exceeded the country target (€1,700,000) by 11.86%. This was driven by sales in Munich (47.19% above target) and Berlin (6.64% above target), and offset by sales in Hamburg (5.62% below target).

Total sales in France were €2,644,929, which fell short of the country target (€2,700,000) by 2.04%. This was driven by sales in Lyon (9.79% below target) and Strasbourg (3.67% below target), and offset by sales in Paris (1.3% above target).

Example 2 — Same measure from different datasets

Alternatively, you can compare the same measure (column) aggregated from two different datasets — that is, two datasets created from the same table using the filterRows function.

For example, you could split the table used above into two distinct datasets: one for Q2 data, the other for Q1 data. You could then input these to the BASE DATA and COMPARISON DATA parameters, then input the Sales column to both the BASE MEASURE and COMPARISON MEASURE parameters. This will compare the sales figures for Q2 against the sales figures for Q1.

To simplify the ATL, use the filterRows function to create two different table regions, then convert the function calls to global script variables that you can input to the groupByVarianceTable function.

[[

global.Q1data = filterRows(WholeTable,'Quarter', x -> x == 'Q1'); 
global.Q2data = filterRows(WholeTable,'Quarter', x -> x == 'Q2');

groupByVarianceTable(Q2data,Q1data,Sales,Sales,(Country,City),1)

]]

The ATL above uses Sales (for Q2 rows only) as the base measure and Sales (for Q1 rows only) as the comparison measure. The grouping dimensions are Country and City. The direction percentage threshold is 1%.

The result is a multi-level ATL object similar to that produced in Example 1. Again, you could extract key data insights from the object by using dot and/or bracket notation and ATL functions:

[[

global.Q1data = filterRows(WholeTable,'Quarter', x -> x == 'Q1');
global.Q2data = filterRows(WholeTable,'Quarter', x -> x == 'Q2');

global.results = groupByVarianceTable(Q2data,Q1data,Sales,Sales,(Country,City),1);''

]]

Total sales for Q2 were [[currencyFormat(results.baseValue,'EUR')]]. Compared to the Q1 total of [[currencyFormat(results.comparisonValue,'EUR')]], this represents an increase of [[abs(results.variancePercentage)]]%. This was driven by sales in [[map(results.drivers, x -> joinStrings(x.dimensionInstance," (up ",abs(x.variancePercentage),"%)"))]] and offset by sales in [[map(results.offsets, x -> joinStrings(x.dimensionInstance," (down ",abs(x.variancePercentage),"%)"))]].

Q2 sales in [[results.drivers[0].dimensionInstance]] were [[currencyFormat(results.drivers[0].baseValue,'EUR')]], which exceeded Q1's sales ([[currencyFormat(results.drivers[0].comparisonValue)]]) by [[abs(results.drivers[0].variancePercentage)]]%. This was driven by sales in [[map(results.drivers[0].drivers, x -> joinStrings(x.dimensionInstance," (up ",abs(x.variancePercentage),"%)"))]], and offset by sales in [[map(results.drivers[0].offsets, x -> joinStrings(x.dimensionInstance," (down ",abs(x.variancePercentage),"%)"))]].

Q2 sales in [[results.offsets[0].dimensionInstance]] were [[currencyFormat(results.offsets[0].baseValue,'EUR')]], which were down on Q1's sales ([[currencyFormat(results.offsets[0].comparisonValue,'EUR')]]) by [[abs(results.offsets[0].variancePercentage)]]%. This was driven by sales in [[map(results.offsets[0].drivers, x -> joinStrings(x.dimensionInstance," (down ",abs(x.variancePercentage),"%)"))]], and offset by sales in [[map(results.offsets[0].offsets, x -> joinStrings(x.dimensionInstance," (up ",abs(x.variancePercentage),"%)"))]].

The text output is:

Total sales for Q2 were €2,277,852. Compared to the Q1 total of €2,268,619, this represents an increase of 0.41%. This was driven by sales in France (up 1.97%) and offset by sales in Germany (down 1.73%).

Q2 sales in France were €1,335,379, which exceeded Q1's sales ($1,309,550) by 1.97%. This was driven by sales in Lyon (up 8.95%) and Strasbourg (up 5.48%), and offset by sales in Paris (down 1.5%).

Q2 sales in Germany were €942,473, which were down on Q1's sales (€959,069) by 1.73%. This was driven by sales in Hamburg (down 5.03%) and Berlin (down 2.12%), and offset by sales in Munich (up 2.06%).

Important

Both examples above are derived from THIS DOWNLOADABLE EXAMPLE PROJECT.

The example project uses the same data and global script variables shown above; however, it also uses multiple scripts and conditional statements to cover other possible data scenarios (e.g. a variance having no offsets). It therefore provides a more detailed example of how this function might be used in a proper project.

Printing parts of the result

Printing the whole result object (while developing your narrative) produces a large output that is difficult to read. Printing select parts of the object is useful because it allows you to see only the part you are interested in at a particular point in development.

The examples below assume you have assigned your result to a global script variable named results (as in the examples above).

  1. Prints the dimension names for the drivers (level 2) of the top-level variance:

    [[map(results.drivers, x -> x.dimensionInstance)]]

  2. Prints the number of offsets (level 2) of the top-level variance:

    [[len(results.offsets)]]

  3. Prints the number of drivers (level 2) of the top-level variance:

    [[len(results.drivers)]]

  4. Prints the dimension name for each level 2 driver, plus each variance amount:

    [[map(results.drivers, x -> joinStrings(x.dimensionInstance, ': ', x.varianceValue))]]

  5. Prints the dimension name for each offset of the first level 2 driver, plus each variance amount:

    [[map(results.drivers[0].offsets, x -> joinStrings(x.dimensionInstance, ': ', x.varianceValue))]]

  6. Prints the dimension name for each offset of second level 2 driver, plus each variance amount.

    [[map(results.drivers[1].offsets, x -> joinStrings(x.dimensionInstance, ': ', x.varianceValue))]]

  7. Prints the drivers of the top-level variance that were above the threshold variance percentage (set in 6th parameter):

    [[filter(results.drivers, x -> x.direction != 'NOCHANGE')]]

  8. Prints the dimension name of the first offset (level 4) of the second offset (level 3) of the third level 2 driver:

    [[results.drivers[2].offsets[1].offsets[0]]

Note

Indexes into lists of drivers and offsets are zero-based. Therefore, in the last example above, results.drivers[2] is accessing the third level 2 driver rather than the second.

Important

If you try to reference an object using an index value that doesn't exist, the function will return an error like: "MAJOR warning in Main: Index out of bounds. The index 2 refers to a non-existent element; collection size = 0."

Using conditionals

When using an index to reference a particular item in a result object, it's good practice to use a conditional statement to check whether there are any items in the relevant list.

A variance will always have at least one driver, but it might have no offsets. To cover this possibility, you can create conditional statements using the len function. For example:

[[if(len(results.offsets)>0){This was driven by sales in [[map(results.drivers, x -> x.dimensionInstance)]] and offset by sales in [[map(results.offsets, x -> x.dimensionInstance)]]}else{This was driven by sales in [[map(results.drivers, x -> x.dimensionInstance)]]. There were no offsets}]].

Note

To keep the ATL simple, conditionals are not used in Example 1 and Example 2.

Conditionals are used in the DOWNLOADABLE EXAMPLE PROJECT. The project also illustrates how to loop through all the drivers or offsets in a list, describing each one with a subscript.

© Arria Data2Text Limited