Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

Often you need to create conditional aggregations in QlikView, e.g. when you want to create a graph that shows this year’s numbers only, also if there are several years possible.

 

Image1 count.png

 

There are basically three ways to do this

  • A conditional expression outside the aggregation function, e.g. If(<Condition>, Sum(<Expression>))
  • A conditional expression inside the aggregation function, e.g. Sum(If(<Condition>,<Expression>))
  • Set Analysis, e.g. Sum( {<Set Expression>} <Expression> )

If you choose a conditional expression outside the aggregation function, you will have a condition that is evaluated once per dimensional value. Further, all three parameters of the If() function are aggregations, so you need to use aggregation functions, also in the condition, otherwise the expression will not be evaluated the way you want to.

So - don’t use naked field references!

   If( ShippingDate >= vReferenceDate, Sum( Amount ) )   // Incorrect !

   If( Min( ShippingDate ) >= vReferenceDate, Sum( Amount ) )   // Correct

If you instead put the conditional expression inside the aggregation function, you will have a very different situation: First, the condition will be evaluated on the record level of the source data. In other words: You may get performance problems if you have large data amounts.

   Sum( If( ShippingDate >= vReferenceDate, Amount ) )

Secondly, the aggregation function now contains an expression based on several fields (in the above example, ShippingDate and Amount), possibly from several source tables. This means that QlikView will aggregate over the Cartesian product of the included source tables. Normally this is not a problem, but in some odd cases, you will have results different from what you expect.

For instance, if the record with Amount has several shipping dates associated with it, the amount will be counted several times, once per shipping date, and you will get a result that you probably consider incorrect. There is usually a way to get around this problem by writing the expression differently, but if you can’t find one, you should use Set Analysis instead.

The conditional expression can be written in several ways:

  • String comparison:       If( Field = ‘string’, Amount )
  • Numeric comparison:   If( Field = number, Amount )
  • Boolean condition:       If( Flag, Amount )          e.g. Sum( If( IsThisYear, Amount ) )
  • Multiplication:              Flag * Amount              e.g. Sum( IsThisYear * Amount )

The two first examples contain comparisons, whereas the two last contain flags - Boolean fields created in the script. All four ways work fine, but I would recommend avoiding comparisons altogether. Use flags instead. See e.g. Year-over-Year Comparisons for more on flags.

Finally, you can choose to use Set Analysis. This is slightly different from other conditional expressions in that it uses the QlikView selection metaphor for the analysis: First, the Set Expression is interpreted as a selection, whereupon the aggregation is evaluated given this selection.

   Sum( {$<ShippingDate = {">='$(vReferenceDate)'"}>} Amount )

   Sum( {$<IsThisYear = {1}>} Amount )

This means that Set Analysis often is faster than using a conditional expression inside the aggregation. It also means that it calculates what you expect, as opposed to a case where an inside condition creates an unwanted Cartesian product.

However, a drawback with the Set Analysis is that it needs to be performed before QlikView performs the aggregation – you cannot have a Set Expression that evaluates to different values for different rows. The work-around is to calculate the condition in the script and store it in a flag.

Bottom line: Define flags in the script. And use Set Analysis.

HIC

 

Further reading related to this topic:

Performance of Conditional Aggregations

14 Comments
mkelemen
Creator III
Creator III

Mathias,

this is a problem of your set modifier - it takes into account your selections. So if you select one [Resource name], the listbox changes accordingly.

You can avoid it by including identifier 1 to get full set (ignore current selections) aggr(only({ 1 <[Is ressource an agent?]={'Yes'}>}[Ressource name]),[Ressource name]).

BR,

  Matus


2,801 Views
Not applicable

Hey Matus, offcourse! I should have thought about that Thx!!

0 Likes
2,801 Views
ssamuels
Partner
Partner

Hi Henric,

I'm looking for a way to create a conditional aggregation in a chart based on a calculated field name.

I have a datamodel containing three fields that can be summarized; Forecast MPC, Forecast TPC, Forecast KG. The datamodel also has an island table called 'Unit' with three values; MPC, TPC and KG.

I want the expression in my chart to use a caIculated field name, where the field name is calculated based ont the selected unit.

For example:

- The user selects unit 'MPC' in a list box

- The chart expression should calculate the expression sum([Forecast MPC])

The field name in the chart expression should be calculated by combining the text 'Forecast' with the selected value from the Unit field.

I know that I could solve this by creating the following expression;

if( Unit = 'MPC', sum([Forecast MPC]), if( Unit = 'TPC', sum([Forecast MPC]),  sum([Forecast KG])))

The drawback of the above expression is that all three aggregated values in the if-then-else are calculated, therefore this is not the best solution if performance is an issue. I'm also aware of the Conditional feature on the Expression tab in the chart wizard. but I need a more dynamic solution.

Any help would be appreciated!

Thanks

Steven

0 Likes
2,801 Views
anderseriksson
Partner
Partner

Try:

Sum([Forecast $(=Unit)])

/anders

0 Likes
2,801 Views
ssamuels
Partner
Partner

Great!

Thanks Anders

0 Likes
2,801 Views
bi_analysts_2
Creator
Creator

Hi Henric,

Very informative article!

However i didn't managed to use the 3rd method (set analysis) in a way that the condition is if the 'ShippingDate' from your example occurred in the last half a year (last 6 months).. 


Thanks,

Tal

0 Likes
2,801 Views