Did you attended the classroom training for QlikView?

I did around five years ago. Two courses which ran for two days each. One course for developer and one for designer (not to be confused with certification). On those courses we learned about the fundamentals of QlikView and it gave a good foundation on which to learn.

If you remember the course you’ll remember sum(sales) or was it sum([sales amount])? Either way you perfected (and possible groaned) your way to basic expression writing. After the course the fun really began as you released even a simple dashboard required more and your journey into set analysis began.

Then you realized set analysis didn’t solve your latest challenge. You needed to use aggr().

Now I’ll be honest, it took me a while to become confident with this function. Perhaps because I couldn’t ‘see’ what was going on I had to visualize something I didn’t understand which, as you could imagine, had me going around in circles.

I quickly resolved the puzzle once I realized what was happening and I could even replicated the function in the data to check my understanding. Aggr() behaved exactly the same as a ‘group by’ in the script. For example if you had some simple data:

Lets say you wanted to discover the total of all the minimum values by country. Ok, I’m not sure when that would come up in real life but in a QlikView script that would be quite simple.

Load

Country

min(Sales) as Sales

Resident Table

Group by Country;

This would give you this table:

Now its a simple Sum(Sales) to get the total.

With the original table you can get the same result using the aggr() function.

sum( aggr ( min ( Sales ) , Country ) )

The aggregate function is creating the same table although this time its in memory So just like we did in the code earlier with the group by clause being country taking the min value.