7 March 2023

# 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.

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.