Forum Discussion

villasenorbritt's avatar
villasenorbritt
Resolver I
3 years ago
Solved

Excluding Time Frames in Visual when Populating Values with a Measure and Rows with Time Bins

I have a measure that is taking seconds, which corresponds with downtime for a specific machine, and turns it into a duration. 

Here is the measure :

Chelsie Eiden's Duration =
// Duration formatting
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = SUM([actualdowntime])
// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
Var FinalDuration = Hours * 10000 + Minutes * 100 + Seconds
RETURN
// We put the hours, minutes and seconds into the proper "place"
//Hours * 10000 + Minutes * 100 + Seconds
FinalDuration
 
I'm using TimeBins as well in my visual. So I have the ShiftDate, 60_Min_Bins, and 15_Min_Bins in the Rows section of matrix and then the production and downtime in the values:

I wrote a calculated column I was wanting to use as a filter since break and lunch times should not be counted towards downtime:

Exclude_Time_Period = IF(
(vwDowntime[Time] >= TIME(8,30,0) && vwDowntime[Time] <= TIME(8,45,0)) && vwDowntime[Shift] = "1st Shift" ||
(vwDowntime[Time] >= TIME(11,0,0) && vwDowntime[Time] <= TIME(11,30,0)) && vwDowntime[Shift] = "1st Shift" ||
(vwDowntime[Time] >= TIME(14,0,0) && vwDowntime[Time] <= TIME(14,15,0)) && vwDowntime[Shift] = "1st Shift" ||
(vwDowntime[Time] >= TIME(23,30,0) && vwDowntime[Time] <= TIME(24,00,0)) && vwDowntime[Shift] = "2nd Shift" ||
(vwDowntime[Time] >= TIME(21,00,0) && vwDowntime[Time] <= TIME(21,15,0)) && vwDowntime[Shift] = "2nd Shift" ||
(vwDowntime[Time] >= TIME(2,30,0) && vwDowntime[Time] <= TIME(2,45,0)) && vwDowntime[Shift] = "2nd Shift" ||
(vwDowntime[Time] >= TIME(17,00,0) && vwDowntime[Time] <= TIME(17,30,0)) && vwDowntime[Shift] = "3nd Shift" ||
(vwDowntime[Time] >= TIME(14,00,0) && vwDowntime[Time] <= TIME(14,15,0)) && vwDowntime[Shift] = "3nd Shift" ||
(vwDowntime[Time] >= TIME(20,00,0) && vwDowntime[Time] <= TIME(20,15,0)) && vwDowntime[Shift] = "3nd Shift" ||
(vwDowntime[Time] >= TIME(22,30,0) && vwDowntime[Time] <= TIME(22,45,0)) && vwDowntime[Shift] = "3nd Shift"
,
TRUE(),
FALSE()
)
 
 
I tried putting this into the Filters pane and selecting False but the times I'm wanting to exclude still show up. I tried adjusting the Exclusion column to the time bins  and not just my regular time column, and even tried adding it into the duration measure, but nothing is working. Any tips?
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    Phew.  Create a calendar table that goes down to that level (ie 48 rows per day) and then based your measures on that new calendar table, using EXCEPT and INTERSECT.

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    what's the biggest bin size you can get away with for this?  half hour slots? 15 minute slots? 5 minutes?

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        Phew.  Create a calendar table that goes down to that level (ie 48 rows per day) and then based your measures on that new calendar table, using EXCEPT and INTERSECT.