How to calculate datediff for values within one column
Jürgen Minwegen Feb 21, 2017 7:23 AMHello,
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[currentindex1] 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.
Current Date = 15.2.2016
the wanted result should look like this.
case number  current status  stepcount  time in status (days) 

1  running  3  41 
2  terminated  1  34 
3  running  3  5 
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

example.xlsx 10.6 KB

example2.twbx 11.6 KB