Download Set Analysis Primer

x

Set Analysis

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?

Join our academy today!

Also, if you want to get started with Qlik Sense Geo Analytics, please attend our upcoming FREE webinar to learn some awesome tricks.

Join FREE Webinar on Qlik Sense Geo Analytics

 

 

 

 

If you enjoyed this article, get email updates (it’s free).

* indicates required
About the author

3 Responses
  1. Shouldn´t your aggr() consider the year? For example, if we have two lines like that, B would be counted
    A, 2019, 600
    B, 2015, 150
    B, 2015, 200
    B, 2017, 300
    B, 2016, 50

    I´ve slightly changed your solution to
    sum({}Sales) and seems fine now

  2. Selçuk Çadır

    Hi,

    if there was an old record, for example

    B, 2014, 150
    A, 2014, 100
    A, 2013, 100
    C, 2013, 150
    C, 2014, 50

    in this case, the same rules apply

    How can we produce solution for last 5 years?

    1. admin

      You can create a variable and use dollar sign expansion in the Set Analysis expression. With variable, you can control # of years