Aggregation for beginners

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:

Country Sales
UK 10
UK 15
USA 20
USA 25
FRANCE 30
FRANCE 35

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:

Country Sales
UK 10
USA 20
FRANCE 30

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.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s