7 March 2023

Visualizing a streak in Qlik Sense

Share this message
Visualizing a sales 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:

Visualization of a scoring streak

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:

Visualizing a scoring streak in Qlik Sense

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}>}
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 1. Straight table with Sales Representatives.

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):

Step 2. Straight table with Sales Representatives and first expression.

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:

Step 3. Straight table with Sales Representatives and aggr expression.

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:

Step 3. Explanation of Aggr function

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

Step 4. Concatenated values in the straight table.

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 5. Replaced the outcome with pictograms.

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 6. Add only 5 last orders

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 7. Sorting the results

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:

Step 8. Finalizing

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

Maarten Anema

Maarten Anema is a Senior BI Consultant at Bitmetric. He is specialized in Qlik and is the spiritual father and frequent writer of the Friday Qlik Test Prep questions on LinkedIn.

Maarten gets energized by working closely with clients and guiding them through the process of transforming data into actionable insights. When he’s not working, Maarten is an avid cyclist and race car driver.

Take your Qlik skills to the next level!

Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.

Functions Qlik

How can we help?

Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have.