3 Replies Latest reply on Jan 17, 2014 10:26 AM by Joshua Milligan

    Sum of Business Hours/Minutes?

    Mark ODonnell

      I have a chart that displays 3 separate measures all based on Date & Time data:

      Sum of Overall Time  (Closed - Open)

      Sum of Duration       (Closed - Approved)

      Sum of Effort           (Work End - Work Start)

       

      Now I have to add a 4th measure Sum of Business Duration.  This is a measure of total minutes available Mon-Fri during business hours, but this information is not stored anywhere in my DB.   Is there a calculation available in Tableau I can use to do this, or do I need to add the data?   I have already created a separate data source that includes holidays so that I can exclude these from the total.

      Thanks in advance...

        • 1. Re: Sum of Business Hours/Minutes?
          Joshua Milligan

          Mark,

           

          I would think you could create a calculated field in the secondary source to calculated the number of minutes.  Assuming the secondary source has a record for every day, with holidays flagged, the pseudo code would be something like:

           

          8 * 60 * (IF [Weekend or Holiday] THEN 0 ELSE 1 END)  //assumes 8 hour work day

           

          Then you could blend at any level of the date and get the business duration.

           

          Regards,

          Joshua

          • 2. Re: Sum of Business Hours/Minutes?
            Mark ODonnell

            That was my initial thought also.  So looks like I will be creating a new source with all calendar days and holidays.  I was hoping to avoid this, but I am sure I will find other uses for this type of data.

            2 columns:  First column date (1/1/2014 - 12/31/2014, second column will be the flag for holiday).

            • 3. Re: Sum of Business Hours/Minutes?
              Joshua Milligan

              Mark,

               

              If the secondary source only has holidays, before you build out a complete list of days, you might consider doing it in reverse: blend, but then subtract from the total where it is a holiday.  It would be something like:

               

              [Holiday Hours]  (calculated field in secondary source)

              8 * 60

               

              [Work Hours] (calculated in the primary source)

              [# Days] * 8 * 60 - ZN(SUM([SecondarySource.Holiday Hours]))

               

              You'll get a NULL for anything that doesn't match on the blend and the ZN will translate NULL to 0.

               

              Regards,

              Joshua