12 Replies Latest reply on Dec 13, 2017 11:54 PM by Emanuele Marcandella

    Bands of delay in Time - Word orders

    Emanuele Marcandella

      Hello everyone, I try to be simple and concise.

      I can't share any packaged workbook.

       

      I need to create a graph like this:

       

      The lines indicates the number of work orders in time that are open and are in delay in that date in a specific band.

       

      My Datasource is like this BUT IN PIVOT FORM:

       

      Work OrderCreated DateClosing DATE
      017623209103/01/201705/02/2017
      017623219403/01/201707/01/2017
      017623229503/01/201718/03/2017
      017623249703/01/201717/02/2017
      017623259803/01/201721/05/2017
      017623282503/01/201703/05/2017
      017623723003/01/201725/03/2017
      017623773503/01/201708/05/2017
      017623783703/01/201720/04/2017
      017624058303/01/201710/03/2017
      017673459910/01/201728/01/2017
      017673897410/01/201703/04/2017
      017674052110/01/201712/04/2017
      017674820810/01/201714/03/2017
      017675233610/01/201718/02/2017
      017675243910/01/201725/01/2017
      017675256110/01/201716/05/2017
      017675286310/01/201716/04/2017

       

      I need to know, in time, the number of work orders for each band of delay I have:

      gg<3

      3<=gg<7

      7<=gg<15

      15<=gg<30

      30<=gg<90

      gg>=90

       

       

      EXAMPLE:

      This WO

      017623209103/01/201705/02/2017

       

      In time and bands of delay is:

       

      gg<33<=gg<77<=gg<1515<=gg<3030<=gg<90gg>=90
      01/01/2017not open yet
      02/01/2017not open yet
      03/01/2017x
      04/01/2017x
      05/01/2017x
      06/01/2017x
      07/01/2017x
      08/01/2017x
      09/01/2017x
      10/01/2017x
      11/01/2017x
      12/01/2017x
      13/01/2017x
      14/01/2017x
      15/01/2017x
      16/01/2017x
      17/01/2017x
      18/01/2017x
      19/01/2017x
      20/01/2017x
      21/01/2017x

       

      I need to count this for every WO.

       

      I'm fully at your disposal for clarifications.

       

      Thanks for any help that you all can provide to me.

      Best Regards

        • 1. Re: Bands of delay in Time - Word orders
          Norbert Maijoor

          Hi Emanuele,

           

          Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

           

           

          1. M1 gg:datediff('day',[Created Date],[Closing DATE])

           

          2. D1 Bands

           

          if [M1. gg]<3 then "gg<3"

          elseif [M1. gg]>=3 and [M1. gg]<7 then "3<=gg<7"

          elseif [M1. gg]>=7 and [M1. gg]<15 then "7<=gg<15"

          elseif [M1. gg]>=15 and [M1. gg]<30 then "15<=gg<30"

          elseif [M1. gg]>=30 and [M1. gg]<90 then "30<=gg<90"

          elseif [M1. gg]>=90 then "gg>90"

          END

           

          Regards,

          Norbert

          • 2. Re: Bands of delay in Time - Word orders
            Emanuele Marcandella

            Hi Norbert,

            first of all thanks for your answer.

             

            Unfortunately it's not what I need... I need a graph over time.

            So I need to know the day X how may work orders was in every band.

             

            The Date axis should be indipendent by the "created date" and "closing date".

            For example if I create a WO the 01/01/2017 and I closed the 12/04/2017 I want to know that:

            • the 15/12/2016 this WO was in the band of delay : NULL (because is not yet open)
            • [...]
            • the 31/12/2016 this WO was in the band of delay : NULL (because is not yet open)
            • the 01/01/2017 this WO was in the band of delay : gg<3
            • the 02/01/2017 this WO was in the band of delay : gg<3
            • the 03/01/2017 this WO was in the band of delay : gg<3
            • the 04/01/2017 this WO was in the band of delay : 3<=gg<7
            • the 05/01/2017 this WO was in the band of delay : 3<=gg<7
            • the 06/01/2017 this WO was in the band of delay : 3<=gg<7
            • the 07/01/2017 this WO was in the band of delay : 3<=gg<7
            • the 08/01/2017 this WO was in the band of delay : 7<=gg<15
            • [...]
            • the 16/01/2017 this WO was in the band of delay : 15<=gg<30
            • [...]
            • the 25/01/2017 this WO was in the band of delay : 15<=gg<30
            • [...]
            • the 30/01/2017 this WO was in the band of delay : 15<=gg<30
            • [...]
            • the 02/02/2017 this WO was in the band of delay : 30<=gg<90
            • [...]
            • the 30/02/2017 this WO was in the band of delay : 30<=gg<90
            • [...]
            • the 10/04/2017 this WO was in the band of delay : gg>90
            • the 11/04/2017 this WO was in the band of delay : gg>90
            • the 12/04/2017 this WO was in the band of delay : NULL (because is closed)

             

            This concept applied to all the service order and resumed in a graph like the first one with the scale of yellow-red.

            • 3. Re: Bands of delay in Time - Word orders
              Norbert Maijoor

              Hi Emanuelle,

               

              Defined a "dummy-dataset" , the approach remains the same...

               

               

              Regards

              Norbert

              • 4. Re: Bands of delay in Time - Word orders
                Emanuele Marcandella

                Maybe I'm not able to explain myself and I think I'm not able to explain better than my last example.

                 

                A Word order with

                - created date = 01/01/2017

                - closed date = 12/04/2017

                you categorize it as gg>90 because it has been in OPEN status for 101 days. But this is not what I need.

                 

                In your graph you are saying: "The number of work orders opened in date X for every maximum band of delay is Y"

                I need instead: "In date X the number of work orders in opened status for every band of delay at that time was Y"

                 

                The same work order, in time, change his own band of delay...

                It is very different because in my point of view it is variable in time.

                 

                With the "dummy-dataset" you defined, the following graph should be the result I need.

                As you can see at the first day it's not possible that there are WO in category different to 0<gg<3 because... they just have been opened.

                 

                 

                I hope that this explain somehow what I need.

                • 5. Re: Bands of delay in Time - Word orders
                  Norbert Maijoor

                  Hi Emanuele,

                   

                  Took me some while to get it clear;) but I think a "got it":)

                   

                   

                  The approach is based on "scaffolding" and ability to use one source (excel) with to sheets and open it based on Legacy Connection

                   

                   

                  Define the join

                   

                   

                   

                  Drag the required objects to the indicated locations. Measure M1. gg and dimension D1. Bands defined as described above and filtered date

                   

                   

                   

                  Regards,

                  Norbert

                  • 6. Re: Bands of delay in Time - Word orders
                    Emanuele Marcandella

                    Sorry but the Join legacy (with <= and >=) works only if the sheets are in the same files? Correct?

                    • 7. Re: Bands of delay in Time - Word orders
                      Norbert Maijoor

                      Hi Emanuele,

                       

                      Yep, let me know if it's approach is applicable in your scenario otherwise we will continue the "search";)

                       

                      Regards,

                      Norbert

                      1 of 1 people found this helpful
                      • 8. Re: Bands of delay in Time - Word orders
                        Emanuele Marcandella

                        I'll try to apply it because my data source is an automatic extraction so to have in the same file I have to do something and right now I don't know why.

                         

                        In any case I have to better check your solution because I think that the result is not what I expect but I'll try to check in your workbook.

                         

                        Thank you by the way

                        • 9. Re: Bands of delay in Time - Word orders
                          Norbert Maijoor

                          Hi Emanuele,

                           

                          Would like to ask Shinichiro Murakami to the table. Shinichiro Murakami, what are the options left when we have to stick to "autmatic  extraction" without "manipulation" of the underlying data-structure/source. Hope to hear from you. Again your valuable input it appreciated

                           

                          Regards,

                          Norbert

                          1 of 1 people found this helpful
                          • 10. Re: Bands of delay in Time - Word orders
                            Shinichiro Murakami

                            HI Emanuele,

                             

                            I hope I understand you request.

                            In this case, each WO need to have all the possible "Date" to judge the status at each "Date".

                             

                            But note that if your date range becomes wide, the number of data becomes really big.

                             

                             

                            Prepare simple date table in excel or whatever, which should cover all the date range you want to show.

                             

                             

                            Assuming you have 10.2 or newer.

                            Copy this date table and paste as data connection.

                             

                            Use Join calculation and put "1" for each.

                             

                             

                             

                             

                             

                            Regards,

                            Shin

                            1 of 1 people found this helpful
                            • 11. Re: Bands of delay in Time - Word orders
                              Emanuele Marcandella

                              I start to give you feedback because it is helpful (i didn't know that I could paste a table as connection). Tomorrow I'll read your answer. For now, thank you

                              • 12. Re: Bands of delay in Time - Word orders
                                Emanuele Marcandella

                                Thank you very much Shinichiro, that is exactly what i needed.

                                 

                                Cna I ask you also the reason why (and the theory) you use 1 = 1 with Inner Join?

                                I've never used this approach and of course I have to try again this method to fully understand the reasons.

                                 

                                Thanks also to Norbert that address to you and follow me at the beginning.

                                 

                                I just miss a think in my view but I think I can fix on my own!!

                                Thanks again all of you!!

                                Cheeers