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

      Hello,

      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)
      1running341
      2terminated134
      3running35

      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.

       

      Regards