Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER 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 II
Champion II

This is will also work

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

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
Partner
Partner

Hi Sam,

Try this:

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

vinieme12
Champion II
Champion II

My version


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

vinieme12
Champion II
Champion II

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