6 Replies Latest reply on Sep 13, 2018 11:08 PM by elias.daniels.0

    Idle time calculation. Full Time Axis display. Lookup. Utilisation

    elias.daniels.0

      Hi Tableau gurus,

       

      I'm reaching out again to find some help out there.

       

      1) Do you guys will know how to calculate the idle time between two activities.

       

      For example: I have a classroom available from 6pm to 2am (next day). Within that period 4 classes took place. I want to calculate the time the classroom was not being used. Classes 14 and 15, can overlap to each other. I just need the latest finishing time between these two.

       

      I was able to achieve part of this by using Fixed and Max, I got the difference between the ending of the last class and the ending of the classroom. But no between classes. I got stuck. Then I was using Lookup, and I got stuck again.

       

       

       

       

      As you can see, the first subject ended at 9:40pm, the next one started 3 mins later. Then once this subject ended, the next one started 72 mins later, etc... and finally, the last subject ended at 1:23 am, leaving the classroom unused for 37 mins until closing time (2AM).

       

      I will like to know the total minutes of unused time, roughly 3+72+ 37 = 112 mins

       

       

      2) Is there a way to show 2AM in the axis without having any records at all? I was trying to achieve this by joining tables, but the sorting was a mess. I just want a visual representation of the whole class time at least. (from 6pm to 2am).

       

      Once again,

       

      I appreciate your help sharing the light!!!

       

      Cheers,

       

      Elias

        • 1. Re: Idle time calculation. Full Time Axis display. Lookup. Utilisation
          Tim Dines

          If these are not DateTime fields, then I would make them so and perform a DateDiff function.  DATEDIFF('day',[subject start],[subject end]).  This should give you the decimal part of the day between the start and end times.

          • 2. Re: Idle time calculation. Full Time Axis display. Lookup. Utilisation
            Michel Caissie

            Elias,

             

            You can get the idle time with

            WINDOW_SUM( if last() = 0

            then

                if DATEDIFF("minute", MIN( [Subject End] ),MIN( DATEADD('hour',2, DATETRUNC('day',[Subject End])) ) ) > 0

                then DATEDIFF("minute", MIN( [Subject End] ), MIN( DATEADD('hour',2, DATETRUNC('day',[Subject End])) ) )

                else 0

                end

            else

                if DATEDIFF("minute",MIN(  [Subject End] ), LOOKUP( MIN( [Subject Start] ),1 ) ) > 0

                then DATEDIFF("minute",MIN(  [Subject End] ), LOOKUP( MIN( [Subject Start] ),1 ) )

                else 0

                end

            end )

            check the computing

             

            So basically,  for the last row (last() = 0)  you do a datediff between  the Subject End  and  2AM  (datetrunc Subject End at the day and add 2 hours).

            For the other rows  do a datediff between the Subject End  and  the Subject Start  of the next row  (loockup  with index 1).

            Make sure to Sum only the positive difference.

             

            For the second question,  the Time dimension would need to have a  row with a value at 2am if you want to display the Time as a discrete value.

             

            If you are willing to change your view a bit (sheet Example(7)) , you can put Hour(Time)  as a continuous axis on the column. You can set the tick marks of the axis at the Hour,  and format the Date with  HH:mm.

            Next you create a calculation  DATEADD('hour',2, DATETRUNC('day',[Subject End])) and you bring it on the Detail shelf. From there you create an invisible reference line using this calculation.  This will add the  02:00  hour  on the axis.

             

            Michel

            • 3. Re: Idle time calculation. Full Time Axis display. Lookup. Utilisation
              elias.daniels.0

              Hi Michel,

               

              Thanks for your quick answer. No way I could have thought of that!

               

              It looks great, but I have more than one classroom. If you check the filter, you can also choose classroom "Class1" , that it has different start and ending time.

               

              Do you think is possible to modify your idle time to multiple start and ending times?

               

              Thanks in advance!!!

              • 4. Re: Idle time calculation. Full Time Axis display. Lookup. Utilisation
                elias.daniels.0

                Hi Tim,

                 

                Thanks for your email. Unfortunately isn't that simple, because I want to calculate the difference between start and end of different rows, not the same row.

                 

                Cheers,

                 

                Elias

                • 5. Re: Idle time calculation. Full Time Axis display. Lookup. Utilisation
                  Michel Caissie

                  Elias,

                   

                  The goal will be to restart the calculation on every Classroom Date  instead of every Classroom code.

                   

                  I made some modification on the idleTime calculation to take into account the different class hours range.

                  I am not sure it covers all the different cases but at least it gives you an idea on how to apply the logic for this type of problem.

                   

                  Pay attention , idleTime is now a nested calculation composed of two calculations and you have to set the computing in both.

                   

                  Also , since you may want to display more than one classroom, with dates spanning on multiple days, your Hour of Time axis will spread on more than one day. You may want to go with  Example(8) where the Time(hour) is independant of the Date.

                   

                  I will be out of the office for the next two weeks, so I might not be able to get back to you for a while if you have other questions.

                  1 of 1 people found this helpful
                  • 6. Re: Idle time calculation. Full Time Axis display. Lookup. Utilisation
                    elias.daniels.0

                    Hi Michel,

                     

                    Once again thanks for your thorough reply.

                     

                    It works great until I choose Class2. As you can see from the example below:

                     

                     

                    Idle time should be around 225 mins. (6pm to 615pm) + (1027pm to 2am).

                     

                    Nevertheless, I appreciate you Idle time rational, It gave me some ideas to apply in other issues.

                     

                    At the end, I decided to find a workaround in excel, and then upload the Idle time as a column.

                     

                    Thanks again, and I hope you had enjoyed your holidays!

                     

                    Cheers,

                     

                    Elias