12 Replies Latest reply on Jul 3, 2018 7:06 AM by Shinichiro Murakami

# Weekly working hours and consecutive working days calculation

Hi,

I have a situation where I have to count the weekly total working hours and maximum consecutive working days in a week based on the daily data.

I calculated both value available. However, i have some issues on consecutive working days, how can i extract the maximum consecutive working days every week ie, Sunday.

Additionally, since the original dataset is big, how can it easily to have the weekly data in a month for further analysis.

I have mocked up the workbook. Thanks for help!

• ###### 1. Re: Weekly working hours and consecutive working days calculation

Hello Kaihei,

I think you over-thought 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

• ###### 2. Re: Weekly working hours and consecutive working days calculation

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 Mon-Sun). Secondly, how can i capture the data every month accordingly?

• ###### 3. Re: Weekly working hours and consecutive working days calculation

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

• ###### 4. Re: Weekly working hours and consecutive working days calculation

Email notifications were down and now back up, please check latest response.

• ###### 5. Re: Weekly working hours and consecutive working days calculation

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

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 Wed-Sat 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.

• ###### 7. Re: Weekly working hours and consecutive working days calculation

Hi Don , Kaihei

Not 100% sure, but hope this helps.

Thanks,

Shin

• ###### 8. Re: Weekly working hours and consecutive working days calculation

Shin,

Thank you...we’ll see what they say!

• ###### 9. Re: Weekly working hours and consecutive working days calculation

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.

• ###### 10. Re: Weekly working hours and consecutive working days calculation

Like this?

Thanks,

Shin

• ###### 11. Re: Weekly working hours and consecutive working days calculation

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

HI 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.