Nested Set Analysis - Customer bought in both time periods

[samuel_lin]

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!

[vinieme12]

See this sample app

[sunny_talwar]

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)

[sunny_talwar]

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)

[ArunN]

We have built data model and using the below to identify Active, New, Lost etc. I want to build a KPI to show "All Active as of last quarter", "Lost in the most recent Quarter", "New in the most Recent Quarter" by default. The KPI values should change as when you select different products in filter selection. Filters I have is Product Name,  Product Category, Region

Below is my formula behind each metric

=if(aggr(sum(revenue), customer_name, Quarter)>0 and aggr(sum(prev_revenue), customer_name, Quarter)=0, 'New',
if(aggr(sum(revenue), customer_name, quarter)>0, 'Active',
if(aggr(sum(revenue), customer_name, quarter)=0 and aggr(sum(prev_revenue), customer_name, quarter)>0, 'Attrition','Other')) )