Home > Enhancing Visualizations > Custom Expressions > 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:
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).
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 may
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, since
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: 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: