
1. Re: Weekly working hours and consecutive working days calculation
Don Wise Jun 26, 2018 11:32 AM (in response to KAIHEI CHAN)Hello Kaihei,
I think you overthought what it was you needed to do. Please see attached (2018.1 versioning) workbook and below screenshots. If this answers your question, please mark it as correct so that others may find it useful in the future. Thx, Don

Working hours calculation.twbx 56.7 KB


2. Re: Weekly working hours and consecutive working days calculation
KAIHEI CHAN Jun 27, 2018 1:14 AM (in response to Don Wise)Hi Don,
Thanks for your help! I did found out the formula to calculate the weekly working hours.
WINDOW_SUM(SUM([Working Hours]), 6, 0)
However, for the working days, I would like to know the max consecutive working days, rather than working days in a week. ie, 7 days; 14 days, and etc.
IF SUM([Working day]) = 1 THEN PREVIOUS_VALUE(0) + 1 ELSE 0 END
However, I wonder how can i capture the max value in week (every MonSun). Secondly, how can i capture the data every month accordingly?

3. Re: Weekly working hours and consecutive working days calculation
Don Wise Jun 27, 2018 11:34 AM (in response to KAIHEI CHAN)Hello Kaihei,
Please see newly attached and below screenshots. I believe this is what you're looking for. Also, to be very clear, if I were to use your above calculations, the results would be erroneous. Those calculations are not needed so far as I can tell. If this response is correct, please mark it as such so that others can find it useful in the future. Thx, Don

Working hours calculation.twbx 75.9 KB


4. Re: Weekly working hours and consecutive working days calculation
Don Wise Jun 27, 2018 4:21 PM (in response to Don Wise)Email notifications were down and now back up, please check latest response.

5. Re: Weekly working hours and consecutive working days calculation
KAIHEI CHAN Jun 27, 2018 7:12 PM (in response to Don Wise)Hi Don,
Thanks for your prompt reply! I agree the working hours calculation is more strict forward.
However, for the working days, we would like to know maximum consecutive working days in a week. I did understand it is more complicated than counting the working days in a week. And I am open to explore any smarter way to get the relevant data. Thanks!

6. Re: Weekly working hours and consecutive working days calculation
Don Wise Jun 28, 2018 1:25 PM (in response to KAIHEI CHAN)Hello Kaihei,
Unfortunately I wasn't able to get any farther than the newly attached. Spent a number of hours on this and I think that this will likely require some more advanced help due to the need for nested table calculations, to get to the view that you require. I'm asking Shinichiro Murakami to take a look as he's really good at these types of calcs...
Shin, below is the current view and problem. The requirement is to obtain the max number of consecutive days worked but at the Month/Week view (2nd screenshot).
So if a person worked on Monday but not Tuesday, then worked WedSat the value should = 4. If the person worked Monday and Tuesday not Wednesday, worked Thursday and Friday and not Saturday the value should = 2. As those are the maximums of any set of consecutive days.

Working hours calculation.twbx 37.8 KB


8. Re: Weekly working hours and consecutive working days calculation
Don Wise Jun 28, 2018 2:40 PM (in response to Shinichiro Murakami)Shin,
Thank you...we’ll see what they say!

9. Re: Weekly working hours and consecutive working days calculation
KAIHEI CHAN Jul 2, 2018 8:32 PM (in response to Shinichiro Murakami)Thanks Don and Shin! It's really helpful!
However, I have additional inquiry for the period. As i have the data for 1.5 years, and it will be accumulated, so how can i set in the tableau to get the latest 3 months data only.
For example: review the last 3 months (Apr  Jun) data (because of consecutive working days), but export the Jun data only.

11. Re: Weekly working hours and consecutive working days calculation
KAIHEI CHAN Jul 2, 2018 9:59 PM (in response to Shinichiro Murakami)yes, similar concept. i have this question, as the data will be live data "updated monthly". Just want to filter the data period and export the latest data only.
And I wonder if parameter is useful for this case?

12. Re: Weekly working hours and consecutive working days calculation
Shinichiro Murakami Jul 3, 2018 7:06 AM (in response to KAIHEI CHAN)2 of 2 people found this helpfulHI Kaihei
I don't think parameter is useful in case you want to pick the latest value only.
Put the filed of Filter with Hide to the top of Column shelf and "hide".
Not filter our to keep table calc work.
Once data is updated and the table get new latest month, the formula work to hide past months.
If the answer helps, could you mark my answer as correct to closet the thread.
Not from inbox view but from original post.
Thanks,
Shin