3 Replies Latest reply on Dec 20, 2013 12:52 PM by Caleb Smith

# Find the date of the LAST occurance

There's a great write-up of how to find the first date of an occurance here: http://kb.tableausoftware.com/articles/knowledgebase/first-date-purchased

A key part of the solution is building a calculated field that does the following:

DATEDIFF('day',WINDOW_MIN(MIN([Order Date])),MIN([Order Date]))

I followed the instructions given in the write-up and everything works fine. However, for my particular problem, I want to find the LAST date of a particular occurrence, not the first. When I try to modify the calculated field to the following, I get negative numbers, rather than the positives I was expecting.

DATEDIFF('day',WINDOW_MAX(MAX([Order Date])),MAX([Order Date]))

Can anyone tell me what I'm doing wrong here? I still haven't' fully wrapped my head around the various WINDOW functions, so please pardon my ignorance if I'm trying to do something that's completely backward.

Thanks!

• ###### 1. Re: Find the date of the LAST occurance

Wouldn't the calculation for Days since last order date be:

DATEDIFF('day', MAX([Order Date]), TODAY()) ?

Version 8.1 workbook attached.

• ###### 2. Re: Find the date of the LAST occurance

I think you'll want to try flipping the equation around.  The order in which you list variables matters in the DATEDIFF calculation.  You'll want to change the order since you are now looking at Maximums instead of Minimums.

DATEDIFF('day',MAX([Order Date]),WINDOW_MAX(MAX([Order Date])))

1 of 1 people found this helpful
• ###### 3. Re: Find the date of the LAST occurance

The two different methods gave me slightly different results. After looking at the data in Excel and comparing my manual calculations to the Tableau calculated fields, it looks like DATEDIFF('day',MAX([Order Date),TODAY()) is the most accurate (also much simpler!)