Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
samuel_lin
Creator
Creator

Nested Set Analysis - Customer bought in both time periods

Hello All,

I thought this would be simple and read through many posts online still with no luck, but I know that I need to use nested set analysis to achieve this (?)

I am trying to calculate in an expression (like in a Text & Image in sense) that the number of customers who bought in 2016 (between 01/01/2016 and 12/31/2016) also bought in 2017 (between 01/01/2017 and 12/31/2017).

Is there a way to achieve this?

Thank you!

13 Replies
sunny_talwar

May be like this

Count(DISTINCT {<Customers = p({<Year = {2016}>})*p({<Year = {2017}>})>} Customers)

vinieme12
Champion III
Champion III

This is will also work

count({<Customer={"=count({<YearIS={2016,2017}>}Distinct YearIS)=2"}>}Distinct Customer)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
samuel_lin
Creator
Creator
Author

Thank you Sunny and Vineeth.

How do I apply the same method to calculate everyone who's new in 2017? that is, never purchased prior to 2017. I was thinking just to add a - sign, but it doesn't seem that simple...

samuel_lin
Creator
Creator
Author

Thank you Sunny,

How do I apply the same method to calculate everyone who's new in 2017? that is, never purchased prior to 2017. I was thinking just to add a - sign, but it doesn't seem that simple...

samuel_lin
Creator
Creator
Author

Thank you Vineeth.

How do I apply the same method to calculate everyone who's new in 2017? that is, never purchased prior to 2017. I was thinking just to add a - sign, but it doesn't seem that simple...

sunny_talwar

May be this

Count(DISTINCT {<Customers = p({<Year = {2017}>})-p({<Year = {2016}>})>} Customers)

OmarBenSalem

Hi Sam,

Try this:

count({<Customer=P({<[Year]={2017}>})> - <Customer=P({<[Year]-={2017}>})>} distinct Customer)

vinieme12
Champion III
Champion III

My version


Count(DISTINCT {<Customers = e({<Year -= {2017}>} Customers) >} Customers)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Hi Samuel

Never purchased Prior to 2017 which means min(Year) = 2017 , all the above expressions will give you count of customers who only purchased in 2017 , which is different from No purchase prior to 2017

the expression would be as below for No purchase prior to 2017

=count({< Customers ={"=min(TOTAL <Customers> Year)=2017"} >} distinct Customers)

stalwar1‌ your thoughts

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.