2 of 2 people found this helpful
Take a look at my table ('SR' Tab)
and let me know if it's classifying each customer/month correctly.
If so I'll (try!) and explain how it all works
Thanks, that's exactly what I'm looking for! Looking through the twbx I get a sense of how it's supposed to work, but an explanation would certainly help me out.
Cool...I'll do my best!
So the first thing to do was trigger a thing called 'Domain Padding'. This is a feature that can be applied to "Range Aware" pills (that is dates and bins, so called as they know their Min, Max and Increment), ans means Tableau "densifies" all the marks inbetween
We do that by selecting "Show Missing Values"
And we can use Table Calculations to access the densified marks (which don't really exist in the data). This is what [First Access Date Month - DENSE] is, it's the LoD getting the first purchase and "densifying" it so it takes the value for all marks, not just the ones with data. We can then use a combination of rules to classify the Customer/Months (I've commented out what each does)
IF MIN([Date Month]) = MIN([First Access Date Month]) THEN 'New'
//Simple one, it is the first month of purchase
ELSEIF ISNULL([First Access Date Month - DENSE]) THEN ''
//Blank field if it's before the first month of purchase (as we've not used the DENSE version it's NULL if before the first date of data)
ELSEIF ZN(WINDOW_SUM(COUNT([Date Month]),-[In Active Threshold (Months)],0)) >=1
AND ZN(WINDOW_SUM(COUNT([Date Month]),-[In Active Threshold (Months)],0)) < [In Active Threshold (Months)]THEN 'Inactive'
//Counts up the number of months from the current month and 3 months back (although I've set this to a parameter) to see if they have purchased before, but not more than 3 months ago
ELSEIF ISNULL(MIN([Date Month])) THEN 'Lapsed'
//after all the other checks if this is NULL then they have Lapsed (aka not used for >3 Months)
ELSEIF NOT(ISNULL(LOOKUP(MIN([Date Month]),-1))) THEN 'Repeat'
//Did they purchase the Month before
//Final Catch All, no cells should equal this, but kept in to ensure I had captured all situations (if I saw a 1, then there was a situation I'd not accounted for)
Hope that makes, some, sense!