Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

samuel_lin
Contributor

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!

Tags (1)
12 Replies

Re: Nested Set Analysis - Customer bought in both time periods

May be like this

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

vinieme12
Esteemed Contributor II

Re: Nested Set Analysis - Customer bought in both time periods

This is will also work

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

samuel_lin
Contributor

Re: Nested Set Analysis - Customer bought in both time periods

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
Contributor

Re: Nested Set Analysis - Customer bought in both time periods

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
Contributor

Re: Nested Set Analysis - Customer bought in both time periods

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

Re: Nested Set Analysis - Customer bought in both time periods

May be this

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

OmarBenSalem
Esteemed Contributor

Re: Nested Set Analysis - Customer bought in both time periods

Hi Sam,

Try this:

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

vinieme12
Esteemed Contributor II

Re: Nested Set Analysis - Customer bought in both time periods

My version


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

vinieme12
Esteemed Contributor II

Re: Nested Set Analysis - Customer bought in both time periods

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

Community Browser