Visualizing a streak in Qlik Sense

Last week I saw the following post by Michael Tenner where he got inspired by a Bundesliga chart and recreated that in Power BI. The visualization is really nice and on point, showing the last 5 games played by a football team and whether they are on a winning or losing streak. Not only do I really think that the visualization in itself is neat and could very much benefit many organizations, but the coding in Power BI struck me as well. I’m no Power BI expert, so therefor I decided to see how this could be done in Qlik Sense.

The original:

First let’s review the original chart that inspired the post:

This chart shows us the current standing in the Dutch Eredivisie (Dutch Premier League). It shows the current standing, some statistics around wons/loses/etc. and, in the final column, the results of the previous five games. It’s a a pretty cool visualization. We can immediately see that Ajax is on a winning streak. On the other hand, Twente needs to step up their game to remain in the top, having only won a single game recently.

Let’s take this idea and convert it into a business case. For example; do our customers reorder on a weekly basis? Or does our sales representative score on target for the last couple of orders?

Recreating in Qlik Sense:

So is it possible to create this in Qlik Sense? Yes, it is absolutely possible:

In the chart above we see the sales streak for the previous 5 orders per sales representative and their total revenue. So how did we do this? The expression used is as following:

{<[_Last 5 Orders] = {1}>}
Concat(
Aggr(
If(Sum(#Revenue) - $(vThreshold) = 0, '➖',
If(Sum(#Revenue) - $(vThreshold) > 0, '✅','⛔️'))
, [Order ID])
, ' ', [Order ID])

Braking down the expression:

Compared to the Power BI DAX, the expression above looks relatively simple. So how does it work?

Step 1: Create a straight table

We first make a straight table with the dimension Sales Representative, since we would like to evaluate their sales streak:

Step 2: Set the threshold and expression

Next we set the target per order. In this example we used the variable vThreshold. This could be set to any target you would like:

SET vThreshold = 5000;

To determine whether or not the order has reached the set target we use the expression:

Sum(#Revenue) - $(vThreshold):

And we run into a problem already. This calculation is not working on this level. We now see the total revenue per Sales Representative minus the threshold. However, the threshold is determined on a order level and not total level.

Step 3: Aggregate on order level

To determine the threshold per sales representative on order level we use the Aggr() function to aggregate the expression on the order level:

Nothing is showing? Correct! What Aggr() does is create a sort of mini table in which it calculates the Sum(#Revenue) - $(vThreshold) per Order ID. Below is a visualization of what Aggr() does:

As we can see Aggr() creates a table in memory in which you get the expression per Order ID. However, since we have the Sales Representative as our only dimension in our table, Qlik does not know which of those Order ID’s to show. So it stays empty. Therefor we need to aggregate again. In many cases Min() or Max() are used to show the lowest or largest value respectively. But in this case we use Concat() to combine them:

Step 4: Concatenate the values

The table now shows all outcomes separated by a space, as we have added ‘ ‘ to the Concat() function.

Step 5: Nested If statement

Now let’s make this table more understandable to read by replacing the values with pictograms. Using a nested IF statement, we tell the Aggr() function to evaluate whether or not the target is reached and show a ➖ if the result is exactly zero, otherwise show a ✅ if the target is more then zero, otherwise show a ⛔️, since otherwise the resulting expression will be below zero:

Step 6: Set Analysis

This is already starting to look like it! However, we would like to evaluate the five most recent orders, not all of them. This is where we run into a problem with Qlik. We could use set analysis to get the five most recent orders, but the problem is that set analysis is always on chart level and not on row level. So by doing that we would see only five orders over the whole chart, instead per row. The way it is solved in this example is by calculating the last five Order ID’s per Sales Representative in the script and add this as a flag field to the resulting table. Now we can use the field [_Last 5 Orders] as set analysis:

Step 7: Sort the results

Well this looks like it! However, we are not done yet! The standard sorting is based on the expression low to high. That is why we see al red warning signs in front of the green checks. We would like to sort on the ordering of the Order ID’s latest to newest. This is done by adding the Order ID to the Concat() function, to tell it to sort on that dimension:

Step 8: Finalize the table:

And to finalize the table we add the total revenue to it and sort by that high to low, to see the total performance:

And this is how you can create a scoring streak visualization in Qlik.

More from the Bitmetric team