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

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.

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

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?

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?

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