1 of 1 people found this helpful
I know attached solution is sub-optimal, but thought it might help you a step further.
In addition it might attract other helpers who know better solutions.
This is the formula used:
IF ATTR([EventType]) = 5 THEN DATEDIFF('minute', LOOKUP(MAX(EventTime), 1), MAX([EventTime])) END
Ps. You might find other solutions in FAQ: Open & Close Dates.
Although most of them compared date columns on the same row, I think some compare time between rows.
There was a similar question posted a few weeks, my approach was to run a SQL query on the data and then you can get downtime (in mins), downtime (as a timestamp), etc... on the same record and then Tableau can process it very easily.
This might work for you too, but will depend on how many records you have in the dataset and how are you processing them (Excel, SQL, ????).
Read this, Charting motor runtimes from timestamps? but let me know if you need more help.
I have a similar issue that I need your help on.
What I'd like to do is calculate the time difference between activity TYPE, say time it takes from type= open to type = assignment. If assignment type appears only onces, this calculated field for returning assigned date works fine: IF [Type]= "Assignment" and [TIER LEVEL]="EPM Tier 3" THEN [Datestamp] END
and Open to Assigned calculated will work fine too: DATEDIFF('hour', ([Tickets open by IT Service Desk]),([Assigned Dates]))
However, my data shows that assignment type can occur multiple times at most 3x.
What I am thinking is to created 3 types of Assignment calculated field such as Assigned Date 1 (first time assignment occurred), Assigned Date 2 (2nd time assignment occurred) and Assigned Date 3 (3rd time assignment occurred) but not sure how to modified this calculation: IF [Type]= "Assignment" and [TIER LEVEL]="EPM Tier 3" THEN [Datestamp] END
Can you or anybody help?
Book1.xlsx 9.0 KB
how I get sum(of minutes down) in that case?