4 Replies Latest reply on Dec 28, 2018 6:47 AM by Norbert Maijoor

# Calculating Date Differences

Hello Tableau Community,

I am struggling to calculate date differences in the following case:

I have several cases which can have different statuses. A case can be labelled as follows:

1. Open
2. Blocked
3. Resolved

(If it was reopened, it also shows as Open.)

What I am trying to do is to calculate, for how long was a case blocked, meaning, the difference between the second time a case was opened (29.11.2018 19:26) and blocked (27.11.2018 13:54).

Any idea on how to approach this ?

Thanks a lot!

 Actual Create Date Status CASE ID 09.10.2018 09:08 Open QL-123 20.11.2018 13:45 Resolved QL-123 20.11.2018 13:54 Open QL-123 27.11.2018 13:54 Blocked QL-123 29.11.2018 19:26 Open QL-123 17.12.2018 13:54 Resolved QL-123
• ###### 1. Re: Calculating Date Differences

Good morning

I don't have your twbx workbook so I will walk you through the steps need below

first create and LOD to find the blocked date - something like {fixed [case id]:  max (if status ="Blocked" then max([actual create date]) }  you may need to play with the aggregation

then do the same for Open -

calculate the datediff between the 2 - since the above values are lods you would use something like datediff('minutes", max(blocked lod), Max(open lod) )

Play with it a bit -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculating Date Differences

Hi Martin,

Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread

1. M1. Lookup Actual Create Date: lookup(attr([Actual Create Date]),-1)

2. M2. Lookup Status: lookup(attr([Status]),-1)

3. M3. Delta: (Datediff('second',[M1. Lookup Actual Create Date],attr([Actual Create Date])))/86400

4. Format M3. Delta: custom format hh:mm:ss

Hope it helps,

Regards,

Norbert

• ###### 3. Re: Calculating Date Differences

Hello Norbert,

thanks a lot! I tried this but it does not seem to be working for me. I guess my problem is that the data is very irregular.

In some cases, there are many different actions between 'Open' and 'Resolved'  or 'Blocked' and 'Open' having the same timestamp.

See an example below. I guess I would need something that would be able to look up the timestamp, when 'Data' was Open/Blocked/ Resolved

and compute the time difference on the CASE ID level.

Do you have any idea on how to do that?

Thanks a lot!

 CASE ID Actual Create Date Data QL-123 15.10.2018 13:26:08 xxx xxx xxx xxx xxx xxx OPEN xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx 16.10.2018 09:09:21 xxx 17.10.2018 16:17:03 xxx BLOCKED xxx xxx xxx 17.10.2018 16:17:12 xxx xxx xxx xxx 22.10.2018 09:26:32 xxx xxx RESOLVED xxx xxx 22.10.2018 09:26:37 xxx xxx 11.12.2018 23:32:14 OPEN QL-124 22.11.2018 12:15:35 xxx xxx xxx xxx OPEN xxx xxx xxx xxx xxx xxx xxx xxx 22.11.2018 12:29:05 xxx 22.11.2018 12:30:17 xxx RESOLVED QL-125 26.11.2018 08:05:57 xxx xxx xxx xxx OPEN xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx 26.11.2018 08:06:27 xxx xxx xxx xxx 26.11.2018 10:13:25 xxx 26.11.2018 11:49:29 BLOCKED 28.11.2018 23:48:09 xxx 28.11.2018 23:58:49 xxx 04.12.2018 11:36:55 xxx xxx RESOLVED
• ###### 4. Re: Calculating Date Differences

Hi Martin

Could you share your original workbook in .tbwx format? Then I will be able to play around with it.;)

Regards,

Norbert