9 Replies Latest reply on Jan 17, 2017 8:40 AM by Jamieson Christian

    Graph to show the evolution of Delay Days (Service Orders)

    Emanuele Marcandella

      Hello everyone,

      this is my first question so I hope that I'll do everything correct and I'll find the answer.

       

      I have a list of Service Order (SO). Each has a unique number.

      Related to the service order I have 4 dates:

      - created date (when the service order was created)

      - basic finish date (the limit date we would like to close the service order)

      - closing date (the data when the service order is closed)

      - last data refresh (the last refresh of my database)

       

      Having this 4 data I can define if the service order it is open or closed, if it is in delay or in time, and of course the number of delay days.

      What I need is a graph with time and delay days that will show to me the evolution of the delay days of the service order in the passing time.

       

      Here below a quick example.

       

      DATABASE:    

      Service Order NumberPrioritàCreated DateBasic Finish DateClosing DATELast Data Refresh DateSO Status
      000054083852213/12/201619/12/201622/12/201614/01/2017C
      000054091648220/12/201621/12/201630/12/201614/01/2017C
      000054093965221/12/201622/12/201622/12/201614/01/2017C
      000054094035221/12/201622/12/201622/12/201614/01/2017C
      000054098828227/12/201628/12/201630/12/201614/01/2017C
      000054105287203/01/201710/01/201710/01/201714/01/2017C
      000054105386203/01/201710/01/201710/01/201714/01/2017C
      000054108050205/01/201706/01/201710/01/201714/01/2017C
      000054108260205/01/201710/01/201710/01/201714/01/2017C
      000054108422205/01/201706/01/201710/01/201714/01/2017C
      000054111422209/01/201710/01/201713/01/201714/01/2017C
      000054118775212/01/201713/01/2017 14/01/2017O

       

      The first basic finish date is 19/12/2016 so there are no SO in late until the 19th.

      When the service order is closed it will no more in delay.

      This is the evolution of delay days in time:

         

      Day18/1219/1220/1221/1222/1223/1224/1225/1226/1227/1228/1229/1230/1231/121/12/13/14/15/16/17/18/19/110/111/112/1
      Delay00121234567900000000246012

         

      My graph will be (generated in excel):

       

      How can obtain a graph like this in Tableau with my database?

        • 1. Re: Graph to show the evolution of Delay Days (Service Orders)
          Norbert Maijoor

          HI Joe Oppelt

           

          I kicked this of with a "Scaffold"-dataset with all individual dates to support the visualization on "missing" in between dates but not able to get them "populated" as requested. Could you bring some "light" in the day?

          • 2. Re: Graph to show the evolution of Delay Days (Service Orders)
            Jamieson Christian

            Emanuele,

             

            Example solution attached (Tableau 10.3).

             

            Steps to produce:

             

            Prep work

             

            1. Pivot the "Create Date", "Basic Finish Date", and "CLOSE Date" fields and rename the resulting pivot fields "Date Type" and "Date".

             

            2. Create a calculated field [Date calc]. In my example, this does 2 thing: (1) turns the "Date" string into a date field (because I'm on US locale so Tableau didn't already recognize the European date format); and (2) add 1 day to the "Basic Finish Date" dates to reflect that the service order should start to show late the day after the finish date.

             

            Now the real fun begins. Please note carefully the default table calculations that were configured for each calculation below.

             

            3. Create a calculated field [Late Status Change] to identify changes in late status, based on [Date calc]. "Basic Finish Date" (being the day after the finish date) adds a 1 to the status, and "CLOSE Date" subtracts 1. "Created Date" doesn't do anything.

             

            4. Create a calculated field [Late clamped] which is the running total of the above [Late Status Change], but clamped to ensure it always falls between 0 and 1. (A service order that is closed before its target finish date would push the running total into -1, which we don't want.)

             

            5. Create a calculated field [Late accumulation] which is a running total of [Late clamped]. This is how we accumulate delay days for every day that the service order is late. Note that the running total is multiplied against the current value of [Late clamped] — once it becomes zero (not late) then the running total of late days collapses to zero.

             

            6. The calculations so far have been computed for each individual Service Order Number (soon we'll see how that's dictated in the view). Create a calculated field [TOTAL LATE DAYS] which is simply the sum of the accumulated late days across all service orders. Note the default able calculation differs from the previous calculations.

             

            Now to construct your view…

             

            7. Place [Date calc] on the COLUMNS shelf. Set it to a Measure (DAY) and turn on "Show Missing Values". This will create the appropriate scaffolding for the axis.

             

             

            8. Please [TOTAL LATE DAYS] on the ROWS shelf. It will initially be red, because we are missing a level of detail (Service Order Number) required for the intermediate table calcs.

             

            9. Add [Service Order Number] to the Marks shelf as a Details element, and your chart should look very much like what you modeled in Excel.

             

             

            You will note that this chart has one drawback: during periods when there are no open service orders, it displays a gap in the line, rather than an ongoing zero. I tried a few things, but could not get it to render marks in that part of the chart. Someone else may be able to easily spot where to insert an appropriate ZN() or IFNULL() into the calculations to fix this. Or, it may require more robust scaffolding, such as what Norbert was building out. (I tried to stick to a solution that does not require a second table for scaffolding.)

             

            Still, I hope this helps get you close to what you want to report. See the "Illustration of Calcs" tab on the workbook to see how the intermediate calculations come together to form the final solution.

            2 of 2 people found this helpful
            • 3. Re: Graph to show the evolution of Delay Days (Service Orders)
              Norbert Maijoor

              Hi Jamieson,

               

              Thanks for coming to the table.

              I can't get "my head" around it but there should be a way out based on your calculations in combination with  scaffolding.

              Find my "trial" in attached workbook version 10.1

               

              • 4. Re: Graph to show the evolution of Delay Days (Service Orders)
                Emanuele Marcandella

                Hello Christian,

                first of all thank you for the answer but especially thank you for the long and detailed information you provided to me.

                 

                I'm going to try your solution as soon as possible and I'll be back to you.

                In my database I've already created a Pivot, I've just to figure out how can I "save" my graphs creating another pivot.

                 

                By the way as I said, thank you very much! To both of you!

                • 5. Re: Graph to show the evolution of Delay Days (Service Orders)
                  Emanuele Marcandella

                  I tried and I think that we are close to the solution but not yet arrived.

                  I'll be honest, it's not crystal clear to me al the process because I'm not so expert in tableu (I started using it 2 months ago) and I have some difficulties to understand the table calculations so it's quite hard to me to personalize what you wrote.

                   

                  In any case, I think that the issue is referred to the Service Orders in Open status (so the ones that do not have the 'Closing DATE' filled in).

                  On these Service Orders the delay should be increase if the basic finish date is lower than the last data refresh because I asked to close a SO in date in the past and this is not closed yet so it's in late.

                   

                  I think that my example could be better that that because there was just one SO in Open Status so the graph is the same.

                  In my big database the impact is bigger.

                   

                  By the way I'll try to find a way and I think that it's time to understand what these table calculations are

                  Thanks again

                  • 6. Re: Graph to show the evolution of Delay Days (Service Orders)
                    Emanuele Marcandella

                    I changed the database to make it easier to test.

                    You can find it in the excel file "Example1.xlsx" attached where there is also the graph. I'm sorry if the formula are not the best but I need the result fast so I didn't thought too much.

                     

                    With the new database, this is what I have in Excel:

                     

                    This is what I have in Tableau:

                     

                    I'm almost sure that the differences depends on the not yet closed SO.

                    • 7. Re: Graph to show the evolution of Delay Days (Service Orders)
                      Jamieson Christian

                      Emanuele,

                       

                      Yes, the open orders throw it off. Fortunately, the solution is very simple.

                       

                      1. Change [Data calc] thus:

                       

                      IFNULL([Data],TODAY()) + IF [Data type]='Basic Finish Date' THEN 1 ELSE 0 END

                       

                      This will ensure that if there is no CLOSE date, today's date will be set. (Will ensure that the timeline axis extends all the way to today, which is nice.)

                       

                      2. Change [Late status change] thus:

                       

                      IF ISNULL([Data]) THEN 0 ELSE
                          CASE [Data type]
                          when 'Created Date' THEN 0
                          when 'Basic Finish Date' THEN 1
                          when 'Closing DATE' THEN -1
                          END
                      END

                       

                      This will make sure the status change ignores the fake dates that we injected when CLOSE date is empty.

                       

                      Revised workbook attached. And here's what it looks like:

                      1 of 1 people found this helpful
                      • 8. Re: Graph to show the evolution of Delay Days (Service Orders)
                        Emanuele Marcandella

                        Thank you again.

                         

                        I just correct the "Data calc" because the "Basic finish date" in the future are not good to show:

                         

                        DATA CALC:

                        IF [Date]>=Today()

                        THEN TODAY()

                        ELSE

                        IFNULL([Date],TODAY()) + IF [Date Type]='Basic Finish Date' THEN 1 ELSE 0 END

                        END

                        Thank you again.

                        Now I have to understand in detail, why you did this and how to apply to other cases (for example the evolution of number of service order (not the delay)!

                         

                        Cheers!!!

                        • 9. Re: Graph to show the evolution of Delay Days (Service Orders)
                          Jamieson Christian

                          Emanuele,

                           

                          I'm glad it's working for you! And I'm glad to hear that you have more of an understanding of Tableau's table calculations. They are a powerful feature that can be used for some really cool advanced behaviors, so mastering them will open up a world of possibilities for you.

                           

                          Your "delay days" problem is actually the difficult part. Number of open service orders by day will be easy in comparison. What I posted here was actually an extension of a solution I recently wrote for "number of (active) unique records". See my post and example workbook over here, and use it as an exercise to build a solution for your data set. Re: Distinct Head Counts Between 2 Dates without Running Totals (V.10)

                           

                          Happy viz-ing!