Generate Rows to Find the Missing Hours

[DataPirate26]

Hi All, 

I have one dataset which has List of all Employees Who clocks Hours Each day. I have to Find out the Employees whose Combined Hours Clocking for the day is Less than 8 or That person has not clocked any hours and has missed to fill the timesheet completely. 

There can be 3 scenarios

1. So for employees who will have Date field and Hours Field as blanks that means they have not clocked any hours for the Month. 

2. Employees might have filled first few days of the month and Forgot to Fill the rest of the days. 

Example: Employee has Filled for 1st and 2nd Sept and has missed to fill the rest of the dates. We would need to Find out those dates for which he has forgotten to Fill the timesheet or if he has filled but the cumulative sum of the hours for the day is less than 8. 

3. IF Employee have started filling filling from a given date say 4th Sept we will consider that as the first day would not have to look for 1st,2nd ,and 3rd Sept as That person maybe was in leave. 

Note: Only Workings days are to be considered and weekends needs to be excluded. Filter out all the names who has clocked Less than 8 hours or has forgot to fill the timesheet. For Employees who has missed out "0" shall reflect in the hours Field. Filter should be as such that the date range can be changed dynamically. 

Below Attached is a Sample Data

[DataPirate26]

@Qiu any help on this will be Appreciated 

[binuacs]

@DataPirate26 Can you upload the expected output also?

[DataPirate26]

@binuacs Please check the below attached workflow. I have tried to develop it but have not been able to achieve the desired result totally.

Here one thing more needs to be taken care. For the 2 names that have Date and Hours as Null for those names I have to populate all the working dates(Weekends Excluded)  and Assign Hours = 0 to each dates. As they have not filled the timesheet for not even a single day. 

Also the Filter to select the date range shall be dynamic. So that I can select the Start date and the End date as per my choice.

[Qiu]

@DataPirate26 

Similar with @binuacs , it is better to have the sample output against your input.
I am confused, it should be filter or generate rows?

[OllieClarke]

Hi @DataPirate26 

I think this is what you're after - I followed on with your approach, but added 2 user constants where you can in one place set the start and end of your desired date range

This workflow will find all the days within that range where a user has input fewer than 8 hours

There are other approaches, but I continued on the path you were taking so it all should hopefully make sense

Let me know if you have any more questions

Ollie

[DataPirate26]

@Qiu So we have to use generate rows I guess to track the dates for which No hours has been charged by the Employee.

Filter has to be used to select the date range Like I want to check the data from 1st Sept to 20th Sept (dynamically it should change as per the need of the user). I have attached the workflow above to have an idea of how the Output should come. Along with that there are 2 names for which both Hours and Dates are null that means those users have not clocked hours for a single working day from 1st Sept to the Last date. So all those dates shall be populated and 0 hours needs to be assigned in each date representing they have not filled the timesheet. 

[OllieClarke]

@DataPirate26 This approach also depends on what you mean by dynamic - do you want to set ranges of dates, or would you prefer that those ranges be based on the data/current date?

[DataPirate26]

@OllieClarke This is absolutely what i was looking for. I need to create an app to select the Date range dynamically. You have taken [User End Date] in generate rows. So that will be the Last date for the Selected date range Right and not of that particular user ? 

[OllieClarke]

@DataPirate26 Ah, if you're making an App, then you can use 2 Date interface tools and you won't need the workflow constants.

The [User.EndDate] in the generate rows and filter tool will set the designated final date for all users.

I used the minimum date in the data set to set the starting range for users who had null dates, but I guess you might prefer to use the StartDate constant or date tool.

In that case your workflow would look like this: