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

    Calculating Date Differences

    Martin Lisanik

      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 DateStatusCASE ID
      09.10.2018 09:08OpenQL-123
      20.11.2018 13:45ResolvedQL-123
      20.11.2018 13:54OpenQL-123
      27.11.2018 13:54BlockedQL-123
      29.11.2018 19:26OpenQL-123
      17.12.2018 13:54ResolvedQL-123
        • 1. Re: Calculating Date Differences
          Jim Dehner

          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
            Norbert Maijoor

            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
              Martin Lisanik

              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 IDActual Create DateData
              QL-12315.10.2018 13:26:08xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              OPEN
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              16.10.2018 09:09:21xxx
              17.10.2018 16:17:03xxx
              BLOCKED
              xxx
              xxx
              xxx
              17.10.2018 16:17:12xxx
              xxx
              xxx
              xxx
              22.10.2018 09:26:32xxx
              xxx
              RESOLVED
              xxx
              xxx
              22.10.2018 09:26:37xxx
              xxx
              11.12.2018 23:32:14OPEN
              QL-12422.11.2018 12:15:35xxx
              xxx
              xxx
              xxx
              OPEN
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              22.11.2018 12:29:05xxx
              22.11.2018 12:30:17xxx
              RESOLVED
              QL-12526.11.2018 08:05:57xxx
              xxx
              xxx
              xxx
              OPEN
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              xxx
              26.11.2018 08:06:27xxx
              xxx
              xxx
              xxx
              26.11.2018 10:13:25xxx
              26.11.2018 11:49:29BLOCKED
              28.11.2018 23:48:09xxx
              28.11.2018 23:58:49xxx
              04.12.2018 11:36:55xxx
              xxx
              RESOLVED
              • 4. Re: Calculating Date Differences
                Norbert Maijoor

                Hi Martin

                 

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

                 

                Regards,

                Norbert