How to analyse time passed between order status change??

[andre_ficken]

I have a set of orders by operating company that go through statusses from CREATED up to SHIPPED and INVOICED there are many possible statusses in between. Each status change of an order is logged with date/time and user. What I would like to achieve with this data is to compare 1 status with another status of the same order and show the difference in days/hours/minutes. This could be for 1 order, but could also be for many orders to get to some sort of an 'average time passed since status change' value.

I have looked at the alternate state functionality to do this, but it looks rather complicated and before I start diving into it, I would like to be sure that this is the right solution for this question.

[MarcoWedel]

Hi,

you could create a duration field in the script using Previous() and/or Peek() functions, if necessary on sorted resident loads.

Please post some sample data and your expected result to propose a solution.

thanks

regards

Marco

[andre_ficken]

Hi Marco, Thanks for your reply. I probably need to clearify a bit more. 1 order for 1 customer goes through the process from entry to invoice in a number of steps and in some cases the same step multiple times. eg. from Rejected by planning back to review for planning, followed by submit to planning. So what they would like to measure is the time passed between for example ORDERCREATE and PLANNINGREJECT. The user should be able to select the 2 status codes and the result is calculated based on that. Note that in the case of my previous example the status PLANNINGREJECT and PLANNINGSUBMIT could occur more then 1 time, and so do other status codes. For the calculation always the latest occurence of the status should be taken into the calculation. I will see if I can compile a example set of data and attach it to this question

[andre_ficken]

Additional to this, it is also a request to be able to do this for an orderset over e period of time. So for example: what was the averagetime lapse for ORDERCREATE to PLANNINGSUBMIT for orders between sept 1 and sept 30 for A-Customers, a different group of customers or a single account.

[andre_ficken]

Here is 1 order example screen shot and a multi order (1 Branche) example. The customer does not allow me to attach a full data example.... In Orderstatus 1 the user would select the starting status and in Orderstatus 2 they would select the ending status. For each status there is 1 or more Status DateTIme registrations. What they would like to see is the difference between the first occurrence of status datetime for Order Status1 and the last occurrence of status datetime for Order status 2.

[lakshmikandh]

in data load you separate the columns using status field,if you have fixed number of status,like

select

order_no

case when status='Created' then Date else '' end as Created

case when status='Shipped' then Date else '' end as Shipped

case when status='Invoiced' then Date else '' end as Invoiced

from table

group by order_no

You can use these new fields in expression to get the time difference, average lead time, etc,.

[andre_ficken]

I understand what you mean, but that is not what I was after. If you read my previous post and look at the posted images, you will see that the choice of the 2 statussen to be compared will be chosen by the user. 1 status can be re-run, when the next stage is rejected. So there is no guarantee there is only 1 iteration per status.

The time an order is stuck in 1 status is easily calculated, that I have covered already. I was hoping that someone who knows the way alternate states work, would be able to confirm that it could work to resolve my question.