Few days ago, I received an email from a blog subscriber with an interesting conundrum he is dealing with.
“I am trying to subset data to find all customers who purchased in each of these years (2015, 2016, 2017, 2018, 2019). Basically, the customer would have made at least one purchase in each consecutive year, and did not have gaps in those years. These would be considered good customers because they have been buying from us every year since 2015” — Scott
I am sure Scott is not the only one who has to deal with this dilemma.
I try to dabble with question like this by creating a brand new Qlik Sense document with an inline table.
data:
load * inline [
Customer, Year, Sales
A, 2015, 100
A, 2016, 200
A, 2017, 150
A, 2018, 700
A, 2019, 600
B, 2015, 150
B, 2017, 300
B, 2016, 50
B, 2018, 400
C, 2015, 150
C, 2016, 50
C, 2017, 300
C, 2018, 200
C, 2019, 400
]
;
Next, I created a simple bar chart to show customers who purchased every year since 2015. It is evident from the inline load that customers A and C purchased every year since 2015.
The trick is to use Set Analysis with expression using almighty AGGR function.
Let’s dissect this expression.
In the element set, we have an expression since entire expression has been enclosed between two double quotes.
Within the expression search, we have two sub sets on each side of the equal sign.
On the left side, we are using aggr to create a virtual table with customer name and count of each customer. Since customer A has purchased during all 5 years, aggr will return 5.
On the right side of the equal sign we have count of distinct Year in the dollar sign expression.
Dollar sign evaluates expression and returns 5.
Since the expression returns true, customer A and C will be included in the chart while customer B will not be included.
Problem resolved!
You can download solution app from here
Do you want to keep learning and getting better as a Qlik developer?
Also, if you want to get started with Qlik Sense Geo Analytics, please attend our upcoming FREE webinar to learn some awesome tricks.
