This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Below I have shared a Dataset. In the Provided dataset Hours are being clocked for Each Working days. The weekends are excluded and not being clocked so the dates will be missing as well . I have to check for Each Employee against each date(Working days) if he has clocked a minimal of 8 hours or not . If the hours are less than 8 and the candidate has not clocked any hours for that day that needs to be Flagged.
Note: For a particular date a person can clock Time for Separately for various Tasks. The cumulative sum of the hours need to be 8 for a particular date.
Also a Dynamic Date Filter needs to be created which will help us to Filter out Last 3 months data from Today's Date.
@DataNath The result is still not correct. I have attached 2 Screenshots. The Employee has clocked her Hours on those dates 4th Sept, 5th sept etc. . Yet again for the same Date missing hours have been tracked(0 hours) which is not the case. If for a particular date any hours have been clocked then for that date there would be no missing hours. It would only be Less than 8.
I think maybe somewhere you are missing out I am also not able to track it.
Hey @DataPirate26, that's what the Union + Summarize part of the workflow handles.
This stream (green) is the data containing logged days/hours as is:
This (blue) stream generates all of the days between the min and max date for each staff member, filters out weekends and assigns every day an hour count of 0 to essentially 'create' those days that the employee may have missed when logging:
This central part of the workflow then combines the 2 streams and for every ID/Worker name, groups the days and sums the hours together. For example, Sumita Sanyal in the screenshot above had already logged hours for 2022-07-01, 2022-07-04 etc and my workflow also generated new rows for these dates as part of the missing logs check. However, because of the Summarize, both entries were consolidated into one and the sum taken - as the missing log check has an hour count of 0, this doesn't effect the already correctly logged entry. We can double check that if we just purely look at the results for this worker in this example:
They are still there with the correctly logged number of hours, as expected:
@DataPirate26 after more thought I think the bottom stream & append is actually redundant. All this does is dynamically filter results to be in the months of the date range you’ve selected but the filter at the very start ought to handle this anyway. I think you can take the bottom stream & append fields tool out and also delete the first part of the final filter condition so that you’re only left with the [Daily Hours] < 8 part.
@DataNath Wow this is superb. i have also done it but in a bit different way. I have taken the Max date and the Min Date and Created a Output Range. Then Through a Report text adding the "Text" and combining the 2 Tables. But I was trying to insert 2 blank rows in between. I am setting the order but still the 2 tables are coming in order. the blank rows are staying at Last even after setting the order.