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
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.
in data load you separate the columns using status field,if you have fixed number of status,like
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
group by order_no
You can use these new fields in expression to get the time difference, average lead time, etc,.
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.