13 Replies Latest reply on Feb 21, 2017 7:43 AM by Simon Runc

    How to calculate datediff for values within one column

    Jürgen Minwegen


      I put together a example worksheet of the data I have.
      There are two things I want to show with this data.

      1. How many times was a case in the current status.

      2. What is the total amount of time the case was in the current status (including old ones of same status)


      Ad 1.)

      I managed to show the wanted information with   COUNT(if [New Status]=[Current Status] THEN [Current Status] END)   (columns stepcount)

      Ad 2.)

      Sadly I have no idea how to accomplish this.

      With PowerBI I managed to do this by adding a ID(index) to the queried dataset.

      After that i just looked if the old status equals the current status. If that is the case I calculated the timedifference of   date[currentindex] and date[currentindex-1] and after that added them together for every occasion.

      I didn't find a possibilty to add a ID(index) column to the dataset  (based on this post it is not possible https://community.tableau.com/ideas/3345) and if I add it with the calculated field (ID = Index()) the index changes depending on my filter per row.


      I also tried using previous_value() and lookup() but without success.

      To my understanding  lookup([date],-1) uses the value of the row above. But not the row above in the dataset but the row above in the current spreadsheet.


      the second way i tried to solve this is by adding a new columns with dateold = lookup(MAX([Date]),-1) and then calculating  DATEDIFF('day', [dateold], [Date]). But with that the values also does not calculate based on fixed values.



      Here is a screenshot of some sample data I put together.

      2017-02-21 13_37_54-example.xlsx - Excel.jpg


      Current Date = 15.2.2016


      the wanted result should look like this.


      case numbercurrent statusstepcounttime in status (days)

      I also added a workbook containing the data. (for some reason it does not let me convert the date column to type date)



      I hope someone can help me solve this problem. I am really stuck with my way of thinking.