Qlik Community

Qlik Design Blog

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

Employee
Employee

The Calculation Engine

In a previous blog post, I wrote about Logical Inference and Aggregations, explaining that two different evaluation steps are executed every time you click in QlikView. This post will focus on the second evaluation step – The calculation of all objects.

This is The Calculation Engine.

The Calculation Engine (sometimes called the Chart Engine) is used in all places where you have aggregations. And since you have aggregations in almost every expression, the calculation engine can be invoked from any object: Usually it is invoked when calculating the measure in a chart, but it is also used for labels, for calculated colors, for text boxes, for show conditions, and for advanced search strings.

The calculation engine runs through two steps: First it finds combinations of the values of the fields used in the aggregation function, and, if necessary, builds a temporary look-up table. Then, it performs the actual aggregation using the look-up table to create all relevant combinations. If the aggregation is a measure in a chart or in an Aggr() function, the aggregation is made separately for every dimensional value, using the appropriate scopes.

    1. The Logical Inference
    2. The Calculation Engine (once for every object or aggregation)
      • Find all combinations (create the necessary look-up tables)
      • Aggregate

The different phases can be seen in the picture. The text “Chart” here represents any object with an aggregation, and the text “List box” represents a standard List box without aggregation.

QV06 Multi threading 50.png

Examples:

   Sum( Amount )
In this case, the summation is made in the data table where the field Amount is found. Hence, “finding the combinations” is reduced to looking in this table.

   Sum( NoOfUnits * UnitCost )
In this case, there are several fields inside the aggregation function. If the fields reside in different data tables, QlikView first generates the look-up table for UnitCost using the appropriate key, e.g. ProductID. Then it generates all combinations of the relevant field values using the look-up table – basically a join – and makes the summation on the fly.

   Sum( NoOfUnits * UnitCost ) / Count( distinct OrderID )
The numerator is the same as before (and treated the same) but now there is an additional aggregation in the denominator. So, QlikView will need to generate a help table for this aggregation too, listing the distinct order IDs. For each dimensional value, two aggregations are made, whereupon the ratio between the two is calculated.

   Sum( If( IsThisYear, Amount ))
Flags are often used inside aggregation functions, and usually this is not a problem. However, be aware that QlikView will create all combinations of the two fields before summing, and that this could in odd cases cause duplication of records.

The aggregation step is multi-threaded. However, finding the relevant combinations of field values is currently a single threaded operation, and may occasionally be the bottle-neck when calculating a chart. So be aware of this when you use fields from different tables in the same aggregation function. You might want to consider moving a field to the “correct” table to minimize the impact of this step.

HIC

PS. All of the above is of course true for both QlikView and Qlik Sense. Both use the same engine.

If you want to read more about QlikView internals, see also
Symbol Tables and Bit-Stuffed Pointers
Colors, states and state vectors
Logical Inference and Aggregations
It’s all Aggregations

9 Comments
advait_thakur
Contributor III

Absolutely true HIC

Regards,

Advait

0 Likes
9 Views
brindlogcool
Contributor III

Hi HIC,

As usual an excellent post. If you could add some case study on this along with set Analysis ,Variables on single and Multithreaded.

9 Views
kalyandg
Contributor II

hi HIC,

really very nice blog

Thanks and Regards

Kalyan

0 Likes
9 Views
Arjunarao
Honored Contributor II

Hi HIC,

As usual an excellent post. Thanks for sharing.

0 Likes
9 Views
qlikviewnovice
Valued Contributor II

Excellent!! Thanks for Sharing knowledge.

If you provide with sample it would be better to understand and very helpful.

0 Likes
9 Views
jason_michaelid
Honored Contributor II

"However, finding the relevant combinations of field values is currently a single threaded operation,"

Hi Henric,

Can I just check please - is this still the case? Is the building of the lookup table still a single-threaded operation?

Thanks,

Jason

0 Likes
9 Views
Employee
Employee

In most cases, yes.

The reason why I wrote "currently" is merely that there is an ongoing optimization work taking place. It has happened more than once that I have claimed that the engine works in a specific way, only to find out that the developers already have changed the internals of the engine...

HIC

0 Likes
9 Views
jason_michaelid
Honored Contributor II

That's what I figured and since the post is a couple of years old I thought I'd check. 11.2 was out then so I thought it would still be the case and couldn't see anything in the release notes either.

Many thanks for the quick response sir - hope you are well.

Jason

0 Likes
9 Views
jason_michaelid
Honored Contributor II

Hiya HIC,

We're nearly 2 years on from me asking this question...time for an update! Can you tell us if it is still the case that finding the relevant combinations of field values is a single threaded operation. Or have they worked out how to do this multi-threaded yet?

Thank you sir,

Jason

0 Likes
9 Views