4 Replies Latest reply on Sep 21, 2016 5:37 AM by Akriti Lal

# Date difference fixed userid

Hi everybody,

I find myself into a problem I cannot find a way out.

I have to columns "UserID" & "OrderDate". I need to calculate how many days passed between each order per user.

So for example I need to have for User1 the difference in days between his 2nd and his 1st Order, 3rd and 2nd.

The difference has to be between 2 subsequent orderdates of the same user.

This shouldn't be solved visually, I need the number of days as a calculated field, basically want a 3 column telling me how many days passed from the user previous Order

Attached you can find a simplified excel example.

Ps: the database I'm using is far more complicated than just this 2 columns, the solution should not involve any work on DB to have it fit my need of this case

Any Idea?

Thanks

Mattia

• ###### 1. Re: Date difference fixed userid

Hi Mattia,

Please use the below formula in a calculated field.

if min([User ID]) = LOOKUP(min([User ID]),-1) then

min([Order Date]) - LOOKUP(min([Order Date]),-1)

END

Also see to the attached Tableau Workbook with the same.

Let me know if this helps.

• ###### 2. Re: Date difference fixed userid

You can use the below post to do the calculation between dates. Here basically you need to calculate the difference between two dates

Thanks

Akriti

• ###### 3. Re: Date difference fixed userid

Thanks for your answers. Unfortunately your solutions works just when you put the formula in a visualization that does the grouping by user id on its own.

I have a million different users with millions of transactions, I don't need to have it working in a visualization

I need it to work correctly as a calculated field on the all data source, so that then I can use this calculated field as parameter to filter.

I think there might be a way using function Fixed "userid" but I still couldn't figure out the right way to do it

• ###### 4. Re: Date difference fixed userid

Based on the link I shared earlier I created the below showing the difference from Sample Super store and the difference between the Order Date per Customer Id .