Work ID |Status…|DateInStatus…| Start Date…| End Date | Cycle Time
B-42341 | Analyze…| 5/17/2017
B-42341 | Ready…| 5/29/2017
B-42341 | In Progress…| 6/8/2017…| 6/8/2017
B-42341 | Done…| 7/7/2017
B-42341 | Testing…| 8/8/2017
B-42341 | Accepted…| 8/11/2017…|…| 8/11/2017
I’m trying to calculate the Cycle Time between the time cards go from “In Progress” to “Accepted”. Cycle Time variable returns nothing because most of the values are NULL for Start Date and End Date.
Yes…In-Process = [Start Date] and Accepted = [End Date]
The Cycle Time is the number of days from “In-Process” until the day it arrives in an “Accepted” state.
Your formula is returning a blank in my report. It validates fine, but is not showing anything when I drag it into the table. It shows #MULTIVALUE when I drag it alone in a blank report.
I’m also confused why you are using Max and Min since there is only one start and end date, but you have the right idea about wanting the days between the start and end dates.
It seems if I have both a valid start date and end date in the same row the DaysBetween formula works (when I do it manually with dummy variables).
In the real report “In Process” start date is in one row and the “Accepted” end date is in a different row. I think the DaysBetween formula gets confused with the blank start and/or end entries in the same row.
Start Date = [First Date In Status] Where ([Status]=“In Progress”) End Date = [First Date In Status] Where ([Status]=“Accepted”)
Max and min allow me to “flatten” all the rows that relate to a particular work ID.
The best way to get these working is generally to build up your report table with the components that you want.
Start with [Work ID].
Then add [Start Date] Where ([Status] = “Accepted” - you’ll get multiple rows I’d expect, hence the need to add a Min to flatten it.