Using Expressions on Aggregated Data (the THEN Keyword)


In many cases, you can gain performance by adding a part of an expression to an already aggregated set of data, namely the data used in the current visualization only. 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-db data since there is no row-level data available in that case, but the performance can be improved when working with in-memory data as well.

The part of the expression after the keyword THEN will operate on the part of the expression before THEN. You can include multiple THEN parts within a single expression. In this case, the following THEN expressions will operate on the entire part of the expression before the THEN. The temporary column "[Value]" in a THEN expression refers to the result of the entire previous expression, before the THEN.

Syntax:

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: 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.

 

Recommended: 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 may need to be evaluated after filtering to produce the correct results. Right-click on an axis and select Evaluate Axis On > Current Filtering Only to change the axis evaluation setting in a visualization.

See also:

OVER in Custom Expressions

Axes in Expressions

Advanced Custom Expressions

Expression Shortcuts Overview