7 Replies Latest reply: Oct 6, 2016 9:01 AM by André Ficken RSS

    How to analyse time passed between order status change??

    André 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.

        • Re: How to analyse time passed between order status change??
          Marco Wedel

          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

          • Re: How to analyse time passed between order status change??
            André 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

            • Re: How to analyse time passed between order status change??
              André 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.

              1Order.JPGnOrder.JPG

              • Re: How to analyse time passed between order status change??
                Lakshmikandh Karthikeyan

                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,.

                  • Re: How to analyse time passed between order status change??
                    André 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.