12 Replies Latest reply: Apr 20, 2017 4:48 AM by Sunny Talwar

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!

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

May be like this

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

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

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

May be this

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

• 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)

• 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

My version

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

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

• 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)

• 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)

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

Makes sense, but I think your expression can be simplified to this

=Count({<Customers ={"=Min(Year)=2017"} >} distinct Customers)

and to make it behave like a true set analysis where selection doesn't change the result, you might need to add {1}

=Count({<Customers ={"=Min({1}Year)=2017"}, Year>} distinct Customers)

or something like this might also work

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

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

See this sample app

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

You sample made me realize that we might have to ignore selection in Year field for the expression

=Count({<Person ={"=Min({1}Year)=2017"}, Year>} distinct Person)

or

Count(DISTINCT {<Year, Person = p({<Year = {2017}>})-p({<Year = -{2017}>})>} Person)