Tableau Future and Current References

Question

Tough problem I am working on here.

I have a table of CustomerIDs and CallDates. I want to measure whether there is a 'repeat call' within a certain period of time (up to 30 days).

I plan on creating a parameter called RepeatTime which is a range from 0 - 30 days, so the user can slide a scale to see the number/percentage of total repeats.

In Excel, I have this working. I sort CustomerID in order and then sort CallDate from earliest to latest. I then have formulas like:

=IF(AND(CurrentCustomerID = FutureCustomerID, FutureCallDate - CurrentCallDate <= RepeatTime), 1,0)

CurrentCustomerID = the current row, and the FutureCustomerID = the following row (so it is saying if the customer ID is the same). FutureCallDate = the following row and the CurrentCallDate = the current row. It is subtracting the future call time from the first call time to measure the time in between.

The goal is to be able to see, dynamically, how many customers called in for a specific reason within maybe 4 hours or 1 day or 5 days, etc. All of the way up until 30 days (this is our actual metric but it is good to see the calls which are repeats within a shorter time frame so we can investigate).

Answer 1

I had a similar problem, see here for detailed version Array calculation in Tableau, maxif routine

In your case, that is basically the same thing as mine, so you could apply that solution, but I find it easier to understand the one I'm about to give, I would do:

1) Create a calculated field called RepeatTime:

DATEDIFF('day',MAX(CallDates),LOOKUP(MAX(CallDates),-1))

This will calculated how many days have passed since the last call to the current. You can add a IFNULL not to get Null values for the first entry.

2) Drag CustomersID, CallDates and RepeatTime to the worksheet (can be on the marks tab, don't need to be on rows or column).

3) Configure the table calculation of RepeatTIme, Compute using Advanced..., partitioning CustomersID, Adressing CallDates Also Sort by Field CallDates, Maximum, Ascending. This will guarantee the table calculation works properly

4) Now you have a base that you can use for what you need. You can either export it to csv or mdb and connect to it.

The best approach, actually, is to have this RepeatTime field calculated outside Tableau, on your database, so it's already there when you connect to it. But this is a way to use Tableau to do the calculation for you.

Unfortunately there's no direct way to do this directly with your database.