
1. Re: Space utilization/Finding times within ranges
Shinichiro Murakami Dec 4, 2016 8:20 PM (in response to Joshua Thomas)Hi Jpshua,
It took quite a bit, but I think I finally have answer.
Whichever the tool is, it's quite complicated calculation.
I gave up to analyze your excel formula, but anyways create same functioning formula in Tableau.
Several components.
Normal calc
1) Calculate the First Day Hours based on range hours controlled by parameter
2) Calculate the Last Day Hours based on range hours controlled by parameter
3) Calculate days between excludes first and last day
4) #3 x daily hours from parameters
5) calculate total hours
Weekend Calc
1) Judge the first day and the last day is weekend or not
2) Calculate weekend days between start and end
3) Calculate Weekend hours by #2 x hours from parameters
Normal calculation
[Event Time Range Start]
datetime(date([Reservation Start Date])+[Start Time]/24)
[Event Start Calc]
if [Reservation End Date]<[Event Time Range Start] then Null
else max([Reservation Start Date],[Event Time Range Start])
end
[Hours First Day]
(min([Even End Calc],datetime(date([Reservation Start Date])+[End Time]/24))[Event Start Calc])*24
[Event Time Range End]
datetime(date([Reservation End Date])+[End Time]/24)
[Even End Calc]
if [Reservation End Date] <[Event Time Range Start] then Null
else min([Reservation End Date],[Event Time Range End])
end
[Hours Last Day]
if date([Reservation End Date])>date([Reservation Start Date])
then ([Even End Calc] datetime(date([Reservation End Date])+[Start Time]/24))*24
else 0 end
[Days in between]
max(0,date([Reservation End Date])date([Reservation Start Date])1)
[Hours including Weekend]
[Hours First Day]+[Hours Last Day]+[Days in between]*([End Time][Start Time])
Weekend Calculation
[Event Start Day +1]
MIN(date([Reservation Start Date]+1),DATE([Reservation End Date]))
// Make Start day aligned to Friday (Only Sat,Sun is set as last Friday, other = future Friday)
[Event Start+1 First Friday]
date(datetrunc('week',[Event Start Day +1]+4,"Friday"))
[Event Date End 1]
max(DATE([Reservation Start Date]),date([Reservation End Date]1))
// Make End day aligned to Monday (Only Sat,Sun will be Next Monday, other = past Monday)
[Event End1 Last Monday]
date(datetrunc('week',[Event Date End 1]+2,"Monday"))
[Days of weekend]
min(
[Days in between],
(floor(([Event End1 Last Monday][Event Start+1 First Friday])/7)+1)*2
if datename('weekday',[Event Start Day +1])="Sunday" then 1 else 0 end
if datename('weekday',[Event Date End 1])="Saturday" then 1 else 0 end
)
[Hours Weekend]
[Days of wekend]*([End Time][Start Time])
+ if datename('weekday',[Reservation Start Date])="Saturday"
or datename('weekday',[Reservation Start Date])="Sunday"
then [Hours First Day] else 0 end
+ if datename('weekday',[Reservation End Date])="Saturday"
or datename('weekday',[Reservation End Date])="Sunday"
then [Hours Last Day] else 0 end
[Hours In Range Calc]
[Hours including Weekend][Hours Weekend]*[Include Weekend]
I verified potential variety of Dates with another data between Tableau Calc and your excel Calc.
So I believe the formula works exactly same as your excel formula.
Thanks,
Shin

Weekday_Day_calc_SM_9.2.twbx 197.7 KB


2. Re: Space utilization/Finding times within ranges
W T Dec 9, 2016 2:27 AM (in response to Shinichiro Murakami)1 of 1 people found this helpfulHi Shin,
This looks great. However, I'm currently not able to open the workbook. What is the difference between the Reservation Start Date and Start Time?
Thanks

3. Re: Space utilization/Finding times within ranges
Shinichiro Murakami Dec 9, 2016 5:06 AM (in response to W T)1 of 1 people found this helpfulW T
I don't know,
But in original excel, two measures are exactly same.
Are you using older version of Tableau?
Actually I shared the concept at this link.
EXCEL's "networkdays" s alternative
Thanks,
Shin

4. Re: Space utilization/Finding times within ranges
Joshua Thomas Dec 9, 2016 7:59 AM (in response to Shinichiro Murakami)Hi Shin
Thank you so much. I am still trying to process and play with your TWB to understand what is going on. I think its the correct answer, but just want to understand a little more about your approach. I like your networkdays explanation as well.

5. Re: Space utilization/Finding times within ranges
Shinichiro Murakami Dec 9, 2016 8:25 AM (in response to Joshua Thomas)Don't worry. Take your time.
Thanks,
Shin

6. Re: Space utilization/Finding times within ranges
W T Dec 9, 2016 1:57 PM (in response to Shinichiro Murakami)Are they both calculated fields? or do you mean they are both the exact same date field? The Reservation Date and Start Time...I can see the Reservation Date is most likely your Start/From Date, but don't know what "Start Time" is...I'd like to try to build the Event Time Range Start calculated field as you directed, but have no clue what Start Time is to build the calc

7. Re: Space utilization/Finding times within ranges
Shinichiro Murakami Dec 9, 2016 2:13 PM (in response to W T)You don't need to worry about the difference between two fields.
Just say simply, reservation and start is same in original data set.
if "=" is shown on left side, it's calculated field. If not, it's included in original data.
Thanks,
Shin