Just a point of curiosity -- if a user has transactions such as this:
You'll see that the first 3 qualify -- as do the first 4. Further, transactions 2-through-4 qualify. And again, 5-through-7.
Is the requirement just a binary answer? "This guy had a set of 3 within 90 days." Or would you want to know that he had 2 chunks (or, in this case, even 3 chunks)?
I think we can do something with a PREVIOUS_VALUE function. But I can see a requirement that needs to collect something more than just a binary analysis complicating this.
Also, did you just pick 3 as an example for discussion? Could the requirement actually call for 30 transactions (to pick a number) within a 90 day period?
The requirement is a binary answer - i.e. I just need to know if a person has had 3 transactions in a 90 day period, not how many times a person has had 3 transactions in a 90 day period.
The requirement for now is 3 - I just need to identify people that have had 3+ occurrences in a 90 day period.
Check in the attached,
start by the validateData sheet. Pay attention to the table calculations settings, especially the Nb of Person with threesome, there is a bunch of nested calculations and each of them needs a Computing configuration.
Tableau_Date_Sample ed mc.twbx 36.0 KB
Thanks very much for the detailed solution - glad that it confirms that there was not a simple solution to this problem! I'm going to play around with the concepts in my workbook and see if I can get it working.
Michel gets guys 1 & 3 as well (which is the correct answer). Person 1 has 59 days between transactions on 1/14 and 3/13 so he is in. Person 2 has 98 days between the first and third transactions so he is out. Person 3 has 77 days between transactions on 3/25 and 6/10 so he is in. I will look at your solution as well to see if I can get it working.
Thanks for all the help,