4 Replies Latest reply on Oct 10, 2018 11:30 PM by Deepak Rai

    Creating a Dynamic DateDIFF by Current Status

    Lidia G

      Hi Everyone!

       

      I am currently using Tableau 10 formulas to try to calculate the number of days a record has been at a given status. We pull a report on this information weekly so the table I am creating looks like this (organized from left to right by Record Number, Report Date, Current Status, and my attempt at the days at status count):

       

      Tableau Table.PNG

      Currently the formula is just calculating the date difference from the report date above it. With the following formula:

       

      Tableau Formula.PNG

       

      I would instead like it to calculate the number of work days a record has been at it's current status based on that report date. I am thinking something like the following but my Tableau experience is limited so I apologize:

       

      If First () = 0 Then

           0

      If Current Status (Of the record in the current row) = Current Status (Matching the status from the report just above that row) Then

           DateDIFF( day, Lookup(min(report date)), First row that matches the current row's status), lookup(min(report date)), 0)

      Else

      0

      END

       

      Hopefully that makes sense. Any help is much appreciated!

       

      Edit: Attached is an example of the current problem