Navigation: Flag Descriptions >

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.

 

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/

 

 

 

 

 

 

QSDA Pro version 1.6.0