8 Replies Latest reply on Oct 10, 2016 4:30 PM by Lance Martens

    Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis

    Lance Martens

      Hi All,

       

      I would appreciate any assistance with how to go about formulating a calculated field to have my Delay Duration data distributed on my Date/Time Axis?

       

      Logic:

      • I have a Primary data source (10080_Data) that defines the week in 1min increments = 10080 min (168hrs)
      • I have a Secondary data source (S_Data) that has “Delays” that occurred during the week at random intervals, these events are then plotted onto the date/time axis

       

      Required:

      • I need a calculated field or way to have the “Delay Duration Data” distributed for the actual duration on the date/time axis, not just as a single starting point as I currently have (please see sheet 1 in workbook)
      • Example: If the “Delay Duration was 15 minutes” then the delay needs to be distributed for a period of 15 minutes along the date/time axis relative from when it started
      • For additional context I have also placed an image in the “Example Dashboard” of the type of delay duration distribution I’m after

       

      My workbook is attached.

       

      Any assistance will be most appreciated,

       

      Cheers,

      Lance

        • 1. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
          Łukasz Majewski

          Hi again,

           

          You can do that by:

           

          1. Converting your duration to actual minutes by dividing by 24/60

          2. Showing Gannt Bars with size set to the duration against your [delay start] as minutes

           

          • 2. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
            Lance Martens

            Hi Lukasz,

             

            Again Thanx for the support and insight you provided mate!

             

            The result is 95% of what I’m after,

             

            However is there a way to do the following please?:

            1. Have a full scale fixed 24 hour time X-axis
            2. Create a LOD Calculated Field that will enable me to visualise the “Delay Start date and time + Delay Duration” for each delay event?
            3. On this fixed 24 hour X-axis I will then with the LOD Calculated Field plot the delays as they occur for that period next to each other? And observe “gaps” along the axis where “No Delays” occurred (the image below is an illustration of what I’m aiming for)
            4. Each "Delay Event" has a Start point and runs for a specific duration, I would like to represent this visually in a 24 period

             

             

            Cheers,

            Lance

             

            • 3. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
              Łukasz Majewski

              Like this?

              Again, I do not think you need any LOD here;

              Each delay starts on distinct point in time so it is used to distinguish marks;

              All I did really is calculated a fake date time for axis to extend from 6AM to 6AM next day and used fixed axis range:

              1 of 1 people found this helpful
              • 4. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
                Lance Martens

                Hi Lukasz,

                 

                Legend status bestowed onto you mate, great job!

                 

                You have been able resolve a challenge that has been going for more than three months.

                 

                I have only two quick questions that I need clarification on please?, then it’s a done deal:

                 

                1. Referencing the two images below - How do you get the [Hour of Delay Start] filter to be for “Range of values”?, all I get is “Range of Dates

                • The first image is from your workbook, the second is my attempt in my master workbook

                 

                2. Is there a way to “Fill” the “Blank No Delay Periods” between the blue delay events in with a “Solid Green Colour”?

                • The motivation for this is that the “Blank / No Delay Periods” between the blue delay events is my actual “Operational Time
                • Also! a calculation that can determine the sum total of “Non Delay Time” in the 24hr period? (i.e. SUM of Blank / No Delay Periods)

                 

                 

                Cheers again for your support mate!

                Lance

                 

                • 5. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
                  Łukasz Majewski

                  1. When you drag [delay start] pill to filter shelf set it to HOUR datepart and then change to continuous

                   

                  2.That's tricky and here I used dual axis with a dummy value displayed as bar:

                   

                  But to be honest this viz will not work right in case when you have a delay exceeding 6AM as such mark should be then repeated/extended at the beginning of the axis.

                  So instead of time I think this could be displayed against longitude/latitude as angular dimension is repeated every 2pi....

                  1 of 1 people found this helpful
                  • 6. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
                    Lance Martens

                    Hi Lukasz,

                     

                    Much appreciated for the assistance!

                     

                    I’ve been trying for most of the day to reverse engineer your calculations and methods in my master workbook, and was successful with the normal 24hr axis worksheet.

                     

                    I’ve attached my master workbook and would value your input to replicate what you built yesterday into it (the green colour and the new longitude/latitude as angular “Axis” as you proposed)

                     

                    Master Wordbook Logic (How I use it)

                     

                    In the Filters Shelf:

                    • I define the Process – LW 1750t
                    • I define the Production Unit Type – Longwall
                    • I define the Responsibility – Needs to always included “All” Electrical, Mechanical and Operational measures
                    • I define the “Year” using the Delay Start measure – 2016
                    • I define the Reporting Week measure – Week 39
                    • I define the “24 hour Period” using the Delay Start measure – Date range and Time period defined i.e. 25/09/2016 6:00am to 26/09/2016 6:00am (24hrs)
                    • I define the Scheduled – Needs to always included “All” Scheduled and Unscheduled

                     

                     

                    I have Tree Worksheets in my Master Workbook:

                    1. The first is the "Time 24hr Axis" Original, this one is ok as it is thanks to you mate!

                    2. The second I would appreciate it if you can introduce the longitude/latitude as angular “Axis” in place of the time, and a way then to get the GREEN colouring for the operational time like you suggested

                    3. The Third and last is for the Operational Time, I have been unable to get the two Operational Time (min) and Operational Time (min) displayed calcs to validate

                     

                    Again appreciate your support!

                     

                    Cheers,

                    Lance

                    • 7. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
                      Łukasz Majewski

                      I can see you are using MS SQL Server as data source. Rather than coping with extreme table or LOD calculations which will also affect the dashboard performance you should simply modify tableau data source. In the attached wb I used a the following custom SQL:

                       

                      select z.*, case when x=2 then '1900-01-01 06:00:00' else dt end [24h axis]
                      , case when datediff(MINUTE, '1900-01-02 06:00:00', DATEADD(minute, [Delay (min)], [dt]))>=0 
                          THEN case when x=2 then datediff(MINUTE, '1900-01-02 06:00:00', DATEADD(minute, [Delay (min)], [dt])) 
                          ELSE   datediff(MINUTE, dt, '1900-01-02 06:00:00') END
                        ELSE [Delay (min)] END [Delay Minutes]
                      , d.* from
                      (select case when cast([Delay Start] as time)<'06:00:00' then DATEADD(DAY, 1, cast(cast([Delay Start] as time) as datetime)) else cast(cast([Delay Start] as time) as datetime) end dt
                      , ROUND( [Total Down Time (hrs)]*60,0) [Delay (min)],* 
                      from delay) d
                      inner join (select 1 x union select 2) z ON z.x=1 OR z.x=2 AND DATEADD(minute, [Delay (min)], [dt])>='1900-01-02 06:00:00'
                      

                       

                      Of course you should replace my table names with yours (and possibly adjust datetime strings).

                      This will inject additional records in cases I mentioned before.

                      Here is the end result:

                       

                       

                      • as to the idea with angular axis - this will not work as bars' size cannot be adjusted with Lon/Lat dimensions so I guess it would still be possible but would require drawing shapes which will be very hard to achieve - I doubt it is worth trying
                      • when you attempt to reverse engineer this wb you should verify table calculations' addressing (although they are much simpler now), 'stack marks' and dual axis settings as these may have posed difficulty, I guess
                      1 of 1 people found this helpful
                      • 8. Re: Calculated Field Required to Distribute “Delay Duration Period” on Date/Time Axis
                        Lance Martens

                        Hi Lukasz,

                         

                        It’s in the bag Thanx to you mate!

                         

                        Appreciate your support and patience in seeing this project through to conclusion.

                         

                        My understanding of Tableau has grown exponentially in the last two weeks.

                         

                        PS!

                        Can I ask that you consider “connecting” with me on the Tableau Forum?

                         

                        Cheers,
                        Lance