Using expressions on aggregated data (the THEN keyword)

The use of the THEN keyword makes it possible to evaluate an expression on already aggregated data, usually the data in the current visualization. In many cases, this can give better performance in the calculations. For example, when using OVER expressions there is always a gain to be done by first allowing the data to be aggregated and then applying the OVER part of the expression. This is required when working with in-database data because there is no row-level data available in that case, but performance can be improved when working with in-memory data as well.

In a THEN expression, the already aggregated data is referred to as a temporary column called [Value]. That is, [Value] represents the result of the entire expression before the THEN keyword.

The part of the expression after the THEN uses the result of the part of the expression before THEN, as if it was a column. For example, a custom expression calculating a cumulative sum of sales would be:

Sum([Sales]) THEN Sum([Value] OVER (AllPrevious([Axis.X]))

This expression works for both in-memory and in-database data, whereas the similar Sum([Sales]) OVER (AllPrevious([Axis.X])) only works in memory.

In the THEN expression above, Sum([Sales]) is evaluated first and aggregates data into the temporary column [Value]. The expression following THEN is then calculated based on this temporary column.

Syntax

Aggregated expression (THEN expression) AS [Identifier]
[Value] can be used to refer to the value of the previous expression (either the main expression or a previous THEN expression; there can be multiple THEN in the same expression).
Values on specific hierarchy levels can be referred to using the syntax [X. LevelName] where this represents the value of level "LevelName" in the axis "X" hierarchy. You can use one level reference for each level in each axis hierarchy. For example, to refer to the year in a date hierarchy, use [X.Year].
In THEN expressions you might sometimes need to pick a value from a specific node. In this case you can use [CategoryIndex.X] to pick out the node of interest in an expression. For example, to get the value from node number five on an axis and put it on all other nodes you could use an expression like this:
Sum([Sales]) THEN Sum(if([CategoryIndex.X]=4,[Value],0)) OVER (All([Axis.X]))

The category index for picking the fifth node is set to four, because the first node on the axis will get the category index 0.

THEN expressions can be used on all continuous axes and expressions, like labels, rules etc. However, they cannot be used on categorical axes or together with error bars.

Tip: In the installed client, if your current expression results in numerical values, but the visualization treats them like categories, you can often right-click on the axis in the visualization and select Continuous Scale.

Note: Only use OVER in post-aggregate expressions, if possible.

Examples

Instead of: Sum([Sales]) OVER (AllPrevious([Axis.X]))

Use: Sum([Sales]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

To produce a cumulative unique count for string columns you could use something like this:

UniqueCount([Column]) THEN Sum([Value]) OVER (AllPrevious([Axis.X])) as [Cumulative Count]

Tip: Some axis expressions might need to be evaluated after filtering to produce the correct results. In the installed client, you can right-click on an axis and select Evaluate Axis On > Current Filtering Only to change the axis evaluation setting in a visualization.

See also Shortcuts to frequent custom expressions for more information about expressions using THEN.