5 Replies Latest reply on Feb 28, 2016 7:22 PM by himhim chan

# Count date difference between latest record and the nearest record

Hi all,

I want to find out the date difference between latest record and the nearest record.

However, previous record in the same month will be excluded.

I have the data below:

 A 18-Jan-16 8-Jan-16 15-Dec-15 23-Oct-15 15-Oct-15 14-Oct-15 B 20-Jan-16 19-Jan-16 12-Jan-16 5-Jan-16 31-Dec-15 24-Dec-15 10-Dec-15 24-Nov-15

And I would like to have this result:

A: 18-JAN-16   -    15-DEC-15  = 34  (8-JAN-15 will be excluded)

B: 20-JAN-16   -    31-DEC-15  = 20  (Ignore All JAN data)

I tried Previous_value and lookup of MAX(date) but still not work.

Does anyone know if there is a way to do it?

• ###### 1. Re: Count date difference between latest record and the nearest record

Hi Him

Fun problem, and I haven't yet 100% cracked it...

I can return the 2nd last date, with the days between (yellow) (MIN DATE) where not the same month

or I can return the 2nd last date with days between, but without the same month logic (pink) (MIN DATE 2), that's where I am stuck...

Days calc can be swapped when the MIN DATE works properly...

Thought id share (v9.2.4 attached) where I am, in case

a) its useful

b) someone else (cleverer than me!) can extend/ or replace to give you the full desired functionality...

Interested to see

Cheers

Mark

2 of 2 people found this helpful
• ###### 2. Re: Count date difference between latest record and the nearest record

Are you looking to output a different value for every date in your table, or do you just want something like the following:

A           Value

B           Value

• ###### 3. Re: Count date difference between latest record and the nearest record

Hi Ben,

I want to find out A(Value) and B(Value) like  Mark Fraser 's  example.

• ###### 4. Re: Count date difference between latest record and the nearest record

HI Mark,

Thank you very much! That's exactly what I want.

I can't open the workbook since Im using Tableau 9.0.

Would you mind to share the calculations?

One more question, is it possible to find out the result (value) without table calculation?

Regards,

Him

• ###### 5. Re: Count date difference between latest record and the nearest record

Hi all,

I did it.

Step 1: find out Max Date of each month

Step 2: use Lookup to calculate result

if INDEX()=1

then [Max day]- LOOKUP([Max day], first()+1 )

end

Thanks all of you guys. It is really interesting and useful.

1 of 1 people found this helpful