3 Replies Latest reply on Nov 11, 2016 1:32 PM by Shinichiro Murakami

# average of date diff

HI, I'm looking to find the date diff between customers orders and then the average between all customers.

I've added an example I think explains things better

For example I have 3 customers with difference order dates, I need to find the date diff between each order by customer and then the average against all customers.

I started creating a date diff:

datediff('day',LOOKUP(attr([Date]),-1),attr([Date]))

Then buckets from the date diff calculation, the problem is the final step when you group the date diff by the buckets, is it not possible to have the  average of the date diff (as in excel)?

AVG( datediff('day',LOOKUP(attr([Date]),-1),attr([Date])))

Any help is appreciated!!

Thanks

Amanda

Ex>:

 Customer order date date diff by customer date diff buckets A 01/01/2016 - - This is the results I'm looking for! A 02/10/2016 275 +100 Row Labels Average of date diff by customer A 03/11/2016 32 30-40 1-10 1 A 04/12/2016 31 30-40 30-40 31.2 B 05/02/2016 - - 60-70 62 B 06/03/2016 30 30-40 90-100 93 B 08/07/2016 31 30-40 +100 275 B 09/08/2016 32 30-40 Grand Total 65.22222222 B 10/10/2016 62 60-70 B 07/06/2016 93 90-100 C 11/10/2016 - - C 12/10/2016 1 1-10
• ###### 1. Re: average of date diff

Hey Amanda,

• ###### 2. Re: average of date diff

I am struggling with this same problem.  Every option I try, even following the same steps in the examples you listed above, I get the error "Argument to AVG (an aggregate function) is already and aggregation, and cannot be further aggregated."  This is frustrating because I had it working at some point, and then I went into the book at a later date and it threw a bunch of errors and erased my calculated fields so I cannot go back and find what I ddi previously.  Any suggestions?

• ###### 3. Re: average of date diff

Tableau is not a calculation oriented tool and not good at multiple layer calculation.

So, here is my approach with two tables.  There might be some workaround with using two layers of table calculation, but should be pretty complicated.

!st Table

[Date Diff]

datediff('day',lookup(attr([Order Date]),-1),attr([Order Date]))

[Date Diff Bucket]

if [Date Diff]=0 then "0"

elseif [Date Diff]<=10 then "1-10"

elseif [Date Diff]<=20 then "10-20"

elseif [Date Diff]<=30 then "20-30"

elseif [Date Diff]<=40 then "30-40"

elseif [Date Diff]<=50 then "40-50"

elseif [Date Diff]<=60 then "50-60"

elseif [Date Diff]<=70 then "60-70"

elseif [Date Diff]<=80 then "70-80"

elseif [Date Diff]<=90 then "80-90"

elseif [Date Diff]<=100 then "90-100"

elseif [Date Diff]>100 then "100-"

end

You can handle bucket as dimension in table2.

Thanks,

Shin