Recently, I was contacted by a client enquiring about how they should tackle a Qlik Sense dashboard that would answer a question a senior stakeholder wanted addressing.
Which products haven’t been bought by my top 10 customers?
To paraphrase the stakeholder, they wanted their standard set of KPIs (Sales, Quantity, Stock etc.) but also wanted to know which products haven’t been bought by their top 10 customers. Now, this could be answered relatively easily by providing a chart, sorted by a measure that orders the customers from largest to smallest. Then, selecting the top 10, followed by performing a select excluded on the product field. To see what I mean, view the short clip below:
While this works and is fairly efficient, the client didn’t want to make selections, they wanted that list as soon as they entered the application, something that isn’t uncommon by senior stakeholders. So, let’s go through the solution I came up with – RANK() and E().
Find our top 10 customers:
To begin, we need to identify our top 10 customers. Once we have this piece of information we can determine which products they haven’t bought.
To determine our top 10 customers we will use the RANK function to provide a customer list which we’ll pass to E() (Exclude) function. As per the Qlik Help site: “Rank() evaluates the rows of the chart in the expression, and for each row, displays the relative position of the value of the dimension evaluated in the expression.”
For this to work, we need the top 10 customer list as a single comma-separated string, which can then be used in our E() expression. As RANK() relies upon a chart to evaluate, we need to wrap it in an AGGR function which will provide each customer with a relative position. We will then use CONCAT() to combine the string values. In the example below, I’ve stored the expression in a variable, called vCustomers, to break-down the logic:
Example output:
Using dummy data, this shows the format in which the list is outputted and passed to the next expression.
Find the products the top 10 customers didn’t buy:
Now we have our top 10 customers, we can proceed to find the products they didn’t buy. To do this we will use the E() function, also known as the excluded function. In the example below, E() excludes the values provided from the natural selection state. In short, it performs the solution I mentioned at the start – excludes the top 10 customers from the data and therefore reduces the product values to those not associated to those customers.
Example output:
Using dummy data, this shows the sales for products which were NOT bought by the top 10 customers:
Summary
This solution highlights the power of Qlik’s unique associative engine and how it can be utilised to answer real-world business questions without the need for multiple, and overly complicated, SQL queries. Admittedly, the functions used in this example are more geared towards developers and not business users, though you can clearly see what can be achieved in a couple expressions. If you can’t see yourself writing these expression, then that’s what Qlik partners are for!
Should you want to know more about how Qlik can help you and your organisation feel free to get in touch. Meanwhile, remember to hit the like button if you have enjoyed this post and remember to follow us for more content in the future.
By Chris Lofthouse
Follow @clofthouse89
Comments