7 March 2023 Visualizing a streak in Qlik Sense Share this message 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. More from the Bitmetric team 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 Hoe kunnen we helpen? Barry heeft meer dan 20 jaar ervaring als Data & Analytics architect, developer, trainer en auteur. Hij helpt je graag met al je vragen. Call us Mail us 8 October 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI 25 September 2024 Building Ethical AI: Practical Frameworks for Responsible Innovation AI is transforming industries with innovation and efficiency. But with great power comes great responsibility. The real question is: How do you turn ethical principles into actionable guidelines for AI development? And what steps should your team take to make it happen? AI 17 September 2024 What is AI Ready Data Data quality is all about how accurate, consistent, complete, and up-to-date your data is. If your data is good, you’ll get reliable insights and be able to make smarter decisions. It’s a key part of making sure your AI and machine learning projects are successful. AI Qlik
8 October 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI
25 September 2024 Building Ethical AI: Practical Frameworks for Responsible Innovation AI is transforming industries with innovation and efficiency. But with great power comes great responsibility. The real question is: How do you turn ethical principles into actionable guidelines for AI development? And what steps should your team take to make it happen? AI
17 September 2024 What is AI Ready Data Data quality is all about how accurate, consistent, complete, and up-to-date your data is. If your data is good, you’ll get reliable insights and be able to make smarter decisions. It’s a key part of making sure your AI and machine learning projects are successful. AI Qlik