4 Replies Latest reply on Apr 27, 2017 10:43 AM by Shantanu Mendhekar

Filter on Difference between Dates

Hello all,

I am trying to filter the difference between dates that are in a sequence.

From the screenshot above, the 'Test - Details' sheet has the the date difference between dates in a sequence. For example, for the first SO, the details are between 'Series Sequence #' 1 and 2, 2 and 3, 3 and 4. I am now trying to filter these differences using a slider filter. Also, I want to select a particular date in the 'From Filter', let's say 'SO Line Create Date', and then the difference should be with the next date in the sequence.

I have used the 'Quick Table Calculations' to find the difference between sequential dates as you can see. But, I am stuck on how to make it work as a Calculated Field.

Any help will be appreciated.

Thanks,
Shantanu

• 1. Re: Filter on Difference between Dates

Hi, Shantanu

Please find my solution from attached. the workbook is in 10.2. I also have the snapshot for your reference

Hope this could help.

ZZ

• 2. Re: Filter on Difference between Dates

Thank you, Zhouyi. This works.

I have one more request. I am trying to get the difference between dates on one line, but I am unable to get the 'New From Date' and 'New To Date' on one line. I tried LOOKUP and PREVIOUS_VALUE, but they do not take [Date Type] for aggregation.

Also, can you send me a screenshot, as I have the earlier Tableau version.

• 3. Re: Filter on Difference between Dates

Hi, Shantanu

Not sure how you create the New From Date and New To Date.

below is the screen shot of the end result.

I created the New From Date as:

LOOKUP(ATTR([SO Line Create Date]),0)

Drag this to Rows and set the table calculation as below:

and New To Date as:

LOOKUP(ATTR([Delivery Created On]),1)

Drag this to Rows and set the table calculation as below:

Then I created Index() and drag it to filter, choose the value 2

Then I created a date difference calculation field as:

DATEDIFF('day',[New From Date],[New To Date],'Monday')

Finally, put the date difference to Text and set the table calculation at SO level.

Hope this could help.

ZZ

1 of 1 people found this helpful
• 4. Re: Filter on Difference between Dates

Thanks, Zhouyi Zhang

Your solution works for me, but I will need to test it further. Basically, I want to add the [From Date] and [To Date] parameters, where once I select [SO Line Create Date] then it shows the adjacent date in progression and shows the difference between those two dates. Adding all the dates onto the rows, and hiding them might not be a good idea, but I got the basic knowledge on how to do it.