Ok. Got it.. I will edit it for other datasource using dateparse fuction. I will get back with the updated calc. Thanks for the feedback.
I did it with your calculation and I am not sure is there any typical mistake?
if datepart('hour',[End])>11 (It should be 12 right?)
then min(1,([End]-(date([End])+12/24) )*24) else 0 END
By the way, I am now trying to count the working time by following approaches:
1. if weekday = Mon to Fri then use your formula
2. if weekday = Saturday then count working time 09:00 - 13:00 (no need to count lunch hour)
3. if weekday = Sunday then ignored in calculation
Start: 16/7/2016 (Sat) 10:00
End: 18/7/2016 (Mon) 18:00
Sat: 1300-1000 = 3 hrs
Mon: 1700 - 0900 - lunch hr = 7 hrs
Ans : 3 + 7 = 10 hrs
I still cannot solve this problem after spending a week. Can anyone give me some ideas about it?
Thank you very much.
I have read this article Re: DateDiff for Working days only .
After trying the work days calculation, I am facing a difficulty to combine both calculations together.
Meanwhile, I have prepared a external excel file of the public holidays that helping me to define actual working hour of the staff.
Is that possible to do this calculation in Tableau 9.0?
If you share a sample data - twbx or xlsx - I will implement a solution you seek.
Please find the attached.
There are 2 sheets: 1. Event 2. Public holiday
In "Public holiday" sheet,
Business day = Mon to Fri and not a holiday
Sat workday = Sat and not a holiday
I would like to use the approaches that mentioned before to count the date difference of "Event" sheet.
And use "Public holiday" sheet to exclude Sunday & Holiday in calculation.
Thank you very much.
You provided 2 records and a list of all days in a year with a holiday indicator.
None of the holidays falls within the 2 records' dates.
I will not use this sample...
Sorry for the inappropriate sample.
I have updated 2 events in this sample and I would like to have the result below:
Mon to Fri: 09:00 to 17:00 (exclude lunch time 1200 to 1300)
Sat: 09:00 to 13:00
22/7/2016 (Fri) (1700-1300)=4 hrs
23/7/2016 (Sat) (1300-0900)=4 hrs
24/7/2016 (Sun do not count)
25/7/2016 (Mon) (1000-0900)=1 hrs
30/6/2016 (Thu) (1700-1300)=4 hrs (1200-1300 lunch time)
1/7/2016 (Fri) (Holiday do not count)
2/7/2016 (Sat) (1300-1000)=3 hrs
Thank you for your attention.
1 of 1 people found this helpful
- I used my sample dataset with 1 holiday
- data source is ms access db as my desktop 9.0-9.3 could not establish odbc conection to excel which is required for a custom left join
- the method is exactly the same as the referenced thread detailed; I only simplified the duplicated calculation of [Lunch Time] since weekend days are not relevant here (?)
- should you need further explanation please refer to Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
here is the result:
9.3 wb attached
Thanks, this is exactly what I was looking for.
I found that all parameters' data type are integer. How can I calculate the difference between 0930 to 1730 instead of 0900 to 1700?
On the other hand, I am using Tableau Server Datasource that cannot join with other datasource. Can data blending do the same things?
2 of 2 people found this helpful
Yes, that is how I decided to store HH:mm in parameters. To change it just type 930 & 1730:
As to blending - no it will not work and only left join with <=, >= operators are applicable to this method. The objective is to end up with holiday dates along your start & end dates:
so you got to incorporate it somehow.
You're right! I never tested this thoroughly and missed that %100 yields minutes and not seconds...
Will need to republish corrected version when I have some time
Thanks for spotting this.