3 Replies Latest reply on Sep 13, 2013 6:19 AM by Alex Kerin

# Subtract date ranges from records over time

Hello Tableau community,

While seemingly simple in theory, this has me stumped! I'm looking to find the average days between a customer's transactions recorded on a transactional 'ChangeDate'. I'm sure it has something to do with Lookup, but was unsure how to implement it in this scenario.

Datediff('day',Lookup([ChangeDate], 1), [ChangeDate])   - gives the aggregation error and I'm not sure if the 1 offset will even work when the entries are so spread out.

The arrows on the picture indicate the datediff I'm trying to find.

Thanks for your help, guys & gals • ###### 1. Re: Subtract date ranges from records over time

You will have to deal with sorting and partitioning of the table calc, but first to get the calc right, each [date] needs to be attr([date])

1 of 1 people found this helpful
• ###### 2. Re: Subtract date ranges from records over time

Hey Alex, thanks for the tip, it definitely pointed me in the right direction. Here's the solution I came up with:

1. Start by indexing all of the transactions - Index()

2. Create a calc field :

lookupTest1

Lookup(attr([ChangeDate]), 1)

3. Create another calc field that does the datediff (in hours) and stops when moving on to the next records

Lookuptest2

If lookup([Calculation2],1) < [Calculation2] then

(Datediff('minute', (attr([ChangeDate])),NOW() ))/60

else(Datediff('minute', (attr([ChangeDate])),[LookupTest1]))/60

End

4. Just for good measure, one more calc field to better illustrate the separate records when dropped on the color shelf:

If lookup([Calculation2],1) < [Calculation2] then

1

else 0

End

Here's the result (red is the end of a record)

[edit: status = different transactions on an account] From here we can easily find averages and graph it out.

• ###### 3. Re: Subtract date ranges from records over time

Nice - great job