5 Replies Latest reply on Jul 24, 2017 6:54 PM by swaroop.gantela

    Calculating Overlapping Times

    Ian Lang

      Hi Everyone,

       

      I want to determine whether or not multiple jobs are being run at the same time on a given machine -- in other words, I want to check if time stamps from jobs on a particular machine are overlapping.


      The graph I have has spatial dimensions which don't tell the viewer whether or not there are jobs which overlap.  This is because knowing if jobs are overlapping is secondary to what Is being represented spatially, and the graph would get too messy if overlaps were represented spatially.  It also isn't important to know which jobs are overlapping with each other, just that a given job overlapped with another job.

       

      I attached a workbook which hopefully will make it easier to see what I'm going for.

       

      There is a gantt chart which makes it easy to see how jobs on different machines overlap, and there is a scatter plot that is more similar to what I want to do with my actual data.  The scatter plot is what I'm hoping to color with a calculated field.

       

      Thank you!

        • 1. Re: Calculating Overlapping Times
          swaroop.gantela

          Ian,

           

          Please see if the attached will work for you.

          It uses the methods described here by kettan:

          The specified item was not found.

          and here:

          Filling in the missing dates

           

          Essentially:

          -cross-joining a table with a list of all the possible minutes

          -setting a filter if a minute was being used:

          IF [Date Lookup]>[Start] AND [Date Lookup]<[End]

          THEN [Date Lookup]

          END

          -counting those minutes used

          { FIXED [Machine],[MinuteUsed]:COUNT([MinuteUsed])}

          -setting a status

          IF {FIXED [Job]:MAX([OverlapCount])}=1 THEN "No Overlap"

          ELSE "Overlap"

          END

          • 2. Re: Calculating Overlapping Times
            Ian Lang

            Thank you Swaroop,


            This definitely works, but I unfortunately can't afford to add rows to my data in tableau for every minute being used by a machine.  The data is much too large with significantly larger durations in some instances.  I don't know if there might be another way still?

             

            What I had in mind but couldn't get to work, was a calculation which would check if the next job on the machine started before the current one ended, or if the previous one ended after the current one started.

            • 3. Re: Calculating Overlapping Times
              swaroop.gantela

              Ian,

               

              Please see if the attached may be more feasible.

              This involves a self-join, this time on the Machine Level.

               

              Basically, join all Jobs to all other Jobs on the same Machine.

              Then use this calculated field to see if there is overlap between the two jobs

              by first generating a number to represent the status:

              IF [Job]=[Job (Sheet11)] THEN 3 // Ignore combinations of same job

              ELSEIF [Start]<[End (Sheet11)] AND [End]>[Start (Sheet11)] THEN 1

              ELSE 2

              END

               

              (You may need to add other conditions, but this may be a starting point)

               

              Then Fix the Minimum value of the above to the job.

              If there is no overlap, the min value will be 2.

               

              This can be done as an LOD

              IF { FIXED [Job]:MIN([OverlapPairwise])}=2

              THEN "Non-Overlap"

              ELSE "Overlap"

              END

               

              Or as a Window Calculation

              IF WINDOW_MIN(MIN([OverlapPairwise]))=2

              THEN "Non-Overlap"

              ELSE "Overlap"

              END

              • 4. Re: Calculating Overlapping Times
                Ian Lang

                Hi Swaroop,

                 

                Thank you for your efforts on this!  This seems like it's a more feasible option than before!

                 

                I guess what I really have in mind is something like this (Though I know that this does not work, for many reasons, including a lot of stuff about aggregates)

                 

                IF { Fixed [Machine] : [End] > Lookup([Start], 1) }

                OR { Fixed [Machine] : [Start] < WINDOW_MAX([End], -5, -1) }

                THEN TRUE

                ELSE FALSE

                END

                 

                The idea I'm attempting to implement is a check to see if the current job ends after the next one starts, or if one of the previous few jobs ends after the current job starts.  I chose -5 somewhat arbitrarily, but the idea is that, since it is possible for a machine to run multiple jobs, a job that occurs prior to the previous job, could run longer than the previous job, meaning we can't just check the previous job on the machine, we need to check back a few more.  (It'd be better to not pick a random number, but -5 is more or less a decent choice)

                 

                Do you have any thoughts on this?

                • 5. Re: Calculating Overlapping Times
                  swaroop.gantela

                  Ian,

                   

                  This is likely too convoluted to be useful.

                  Would definitely welcome others' suggestions.

                   

                  I created a parameter to simulate "Now" and then made calculations off of that time.

                   

                  Using the now, it defines a "Current Job" (Now>Start and Now<End).

                   

                  It looks back a certain number of jobs (like your -5; this is set by a parameter).

                   

                  It finds the maximum end time of the jobs in consideration (e.g. within last 5 jobs),

                  and checks that against the Current Job Start.

                   

                  There is an index to figure out which jobs to consider, and there are specific "Compute Using"s

                  to get this to work.

                   

                  The Dashboard is set up such that you use the "Now Mark" parameter slider at the bottom

                  to move through the space and see if the Overlap Status makes sense with respect to the Gantt.