Download Set Analysis Primer

x

Aggregation Functions

Most of us who have worked with both QlikView and Qlik Sense is aware of various aggregate functions that we can use in the script as well as charts.

The family of functions known as aggregation functions consists of functions that take multiple field values as their input and return a single result, where the aggregation is defined by a chart dimension or a group by clause in the script. Aggregation functions include Sum(), Count(), Min(), Max(), and many more.. — Qlik help

These functions are powerful as they provide a single value by taking multiple field values.

For some of these aggregation functions — such as FirstSortedValue(), min() and max() functions — the single value also has an implicit rank. It is one.

We can use a different rank to show second, third, fourth values by changing the rank parameter within these functions.

For example, max(Sales) has an implicit rank value of 1.

max(Sales, 2) will show the second-highest sales.

We can also use a parameterized expression to show the values of the Nth order dynamically.

max(Sales, $1)

If I wanted to display a multi KPI to show a Country with the highest Order amount, I can write an expression with a parameter and store that as a variable. (eSales)

max(aggr(sum(Revenue), OrderID, Country), $1)

Next, I can create another variable, eRank, with a dollar-sign expansion to expand eSales

$(eSales(vRank))

This expression can be a master measure with a dynamic label.

Sales Rank = $(eRank)

Now, I can create multi KPI by using the master measure along with buttons to change rank value dynamically.

Add to this the powerful associative experience by adding a filter and a table to start analyzing sales not only by Country but also by Sales Person or product or customer etc.

Let us help you take Your Qlik skills to the next level!

Become a Pro Member

If you enjoyed this article, get email updates (it’s free).

* indicates required
About the author

Leave a Reply