6 Replies Latest reply on Apr 27, 2017 11:44 AM by Dan Brewer

# Count Transactions within 90 days of each Other

I'm having trouble thinking of a how to solve a question I've been asked. What I need to identify is how many people have had 3 or more transactions within any 90 day period during a given time frame.

For example, if someone has transactions on 1/14/2016, 2/14/2016 and 3/13/2016 then I will want to identify them.

Another person may have transactions on 6/25/2016, 7/25/2016 and 10/1/2016 - I do not want to count them as their 3 transactions did not occur during a 90 day period.

It does not matter what the first/last date is - only that 3 transactions occurred during 90 days, so using min/max functions doesn't seem like it will work. For example, if a user has a transactions on 1/25/2016, 3/25/2016, 5/25/2016, 6/10/2016, and 12/31/2016, I will want to count them. The first 3 transactions (1/25, 3/25, 5/25) do not include him because they did not happen within 90 days. The last 3 transactions (5/25, 6/10, 12/31) do not include him because they did not occur within 90 days. BUT, the middle transactions (3/25, 5/25, 6/10) will include him because they occurred within 90 days.

Any help or clever solutions would be greatly appreciated.

Thanks for any help,

-Dan

Version: Tableau Server 10.2

Tableau Desktop: 10.2.1

• ###### 1. Re: Count Transactions within 90 days of each Other

Just a point of curiosity -- if a user has transactions such as this:

1/1/16

1/5/16

2/2/16

2/20/16

8/1/16

9/1/16

9/15/16

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?

• ###### 2. Re: Count Transactions within 90 days of each Other

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.

• ###### 3. Re: Count Transactions within 90 days of each Other

Dan,

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.

Michel

1 of 1 people found this helpful
• ###### 4. Re: Count Transactions within 90 days of each Other

Michel,

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.

Kind Regards,

-Dan

• ###### 5. Re: Count Transactions within 90 days of each Other

Which are the guys you expect to count?

I get Person #1 and #3.

Michael got guys 2 and 3.

See attached.

And no, it's not a straightforward process to determine.

• ###### 6. Re: Count Transactions within 90 days of each Other

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,

-Dan