Skip to main content
Version: V3.7

Conditional Aggregation

The expression contains a conditional aggregation which may cause poor performance due to multiple aggregation calculations.

Expressions of the form:

If(metric = 'foo', Sum(this), Count(that))

Can be the cause of slow chart performance, as both "Sum(this)" and "Count(that)" will be calculated. If the condition will evaluate to the same true/false for the entire chart, it is wasteful to calculate both the "then" and "else" alternatives.

Guide​

If the condition value is constant for every row of the chart and performance is a concern in this chart, consider replacing the expression with one of these options.

1. Use a variable that delivers the expression as a string constant.​

In the script, create a variable:

SET vChooseCalc = "=If(metric = 'foo', 'Sum(this)', 'Count(that)')";

Note the leading "=" in the variable definition. This is important.

In the chart or Master Measure, reference the variable as:

$(vChooseCalc)

2. Use the "Show Column If" measure property.​

Write each alternative as a separate Measure in the chart. Code an appropriate condition in "Show column if" to control the conditional selection of each measure.

3. Use an Island Table of Expressions​

In the load script, load an island table with an expression and associated lookup value:

Load * Inline [

metric, expr

foo, Sum(this)

bar, Count(that)

]

;

In the chart or Master Measure, use an expression that will select a single expression such as:

$(=FirstSortedValue(distinct expr, 1))

The selection should be enclosed in $(=) to cause the value to be executed as an expression.

Additional Reading:

https://qlikviewcookbook.com/2014/12/how-to-choose-an-expression/

https://qlikviewcookbook.com/2014/12/how-not-to-choose-an-expression/