-
1. Re: Calculating Overlapping Times
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:
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
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
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
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
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.