What is the simplest way to compute for numbers of days between states ?

[mcruz]

I am looking for a way to compute for number of days between states in a workflow.  What would be the simplest option to take.  Also, it would be best if the resulting table can be downloaded to an excel spreadsheet so I can manipulate the data in various ways.

The object is to determine how long a specific state took to complete.

Hoping to get an answer soon.

PS.  I am not a developer or technical person.  I am more into project management.  But if this requires a specific technical approach I can have my developer look at it and try it out.

[tketz · Accepted solution]

hi Manolette,

i think you want a field like "Days in Current State".

Your Administrator has to define an integer field " Days in Current State" with the computation:

IsEmpty(DaysCurrentState(), 0)

With the export items to excel function you can export this field and modify it in excel (im not sure why you want to modify).

If you need an matrix for every state you need a field for every state with computation like:

DaysInState("STATE")

Also you can get the Average Time in a State for a current set of items (Query):

Query("Issues for Average days in state", Project, avg(DaysInState("STATE")))

to display this information you need an item and/or create a report/dashboard

I hope this helps,

best regards,

Tobi

[KaelLizak]

Hello Manolette,

Did Tobias Ketz‌'s answer resolve your issue?  If so, could you click on the Correct Answer link, to let everyone know that his answer solved your problem? 

If not, could you let us all know why it was not helpful, so we can continue to try to help you?

Regards,
Kael

[mcruz]

hi Tobi,

Sorry for not replying sooner as I was busy at work.

Your answer is the same one that my developer suggested which means that we have to introduce new fields to save the number of days a project stayed in each STATE we want to monitor.   If we do this, I am thinking we need to define a trigger in each STATE that will compute the number of days in that state.  So if I modify my workflow and add new STATEs that means I would add new fields and new triggers for these fields to be computed.

I want to ask if there was some other way to get the information without necessarily adding new fields.  I see that each project has a history log which shows the date/time and person who moved the project to the next STATE.  Can we retrieve this History log and maybe download it in Excel.  I was thinking of doing a weekly extraction where I can download logs which pertain to movements to next STATE for a given date range.

If I have this, I could create a pivot table where I could display the STATES as columns for a single project giving me some sort of a timeline.  Once I have this I can then I can define calculation in excel to get number of days between STATES.

Is the history even accessible to the users or is it only for INTEGRITY system use ?

best regards

Manolet

[tketz]

Hi Manolet,

its true, you need for every (new) State a Field with the computation for this state. But you don't need a trigger, you can use the computation field:

as i know you can't export the history to excel. if you want do so, you have to write a trigger and create a new field and/or tab called "logging" (or anything else). on this field your new trigger logs the last modification with modification date (maybe only state changes and only visible for administrators). Now you can export this field informations. you have to calculate the days now in excel or do it before in trigger an log only the days like: "state1:5, state2:18,..."

i think this is possible, but i wouldn't prefer this way.

best regards,

Tobi

[kjain-2]

Hi Tobi,

Just for knowledge purpose, this field will give us value of Days in current state and if it spend less then 24 hour but more then 0 hour then it will give us 0 days , So can we get time in current state that give us data like (Days:Hour:Minute) something like that ?

Waiting for your response Mr. Tobi.

Regards

Kapil Jain

[tketz]

Yes its possible,

have a look at the Administration Guide->Computed Fields

Use SecondsCurrentState() instead of DaysInCurrentState(). Now you have to calculate seconds to hours...

But i don't understand your use-case for that. We calculate this field static (every night). If you want to calculate it live, you need enough performance...

[kjain-2]

Hi Tobi,

Yes i agree with you for not calculating this every time because it can affect performance. As somebody else asks this so i also replied the same and got the function as well to calculate hour in state. (sumTimeEntryByState("State_Name"))

Thanks Tobi for your quick response.

Kapil