Creating a Dynamic Date Filter and Flag the Missouts

[DataPirate26]

Hi All,

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]

How does this look, @DataPirate26?

1 - Filter leverages the DateTimeToday() function and checks that your [Date] field is equal to or later than today's date - 3 months for your dynamic date check

2 - Summarize tool - as you mentioned that people can complete multiple activities on the same day, this aggregates the hours of each ID/worker based on the day to get the total

3 - Filter #2 - This checks whether or not the sum of daily hours is less than 8

Outcome for this example:

False anchor - all at least 8 hours:

Edit: Misread the flag part - that would just require a Formula tool with an expression along these lines. Obviously you can change 'Less than 8' and 'Fine' to whatever you want the flag to be.

[DataPirate26]

@DataNath No this is not exactly I am looking for. If you check the data There would be Few Employees which may have not clocked any hours on a particular date which is a working day. So that date may be be missing for that employee where he have not charged any hours.  I need to figure that out as well. 

Also I need to create a Date Filter (APP based) which will allow me to select my date range. It shall have a starting and an end date. 

[DataNath]

Ok quite a lot more than you mentioned originally then @DataPirate26. How does this look? The first filter is where your app comes into play and prompts the user to select a start and end date.

After that, the workflow takes the minimum date for each worker, generates rows for every day between then and the selected end date. Saturday/Sunday are then filtered out to leave working days and these are all assigned values of 0 hours (so those that match an existing day don't throw off the sum of hours). This is then re-added to the original data source and summarized as before. Matching days won't be affected as the second input all contain 0 hours, whereas working days that weren't previously there will show as 0 hours billed.

[DataPirate26]

@DataNath This is Partially correct. So the data which I have shared is only for the Month of July. In the output I need only the MissOuts checks for that particular month and not the other months. I just wanted to keep a Dynamic Filter so if even if we have last 3 months data it shall take up properly.

Could you update the Workflow to only check the data which is there in the dataset that is for the month of July. I need to pull up the rows or data which Either has not Clocked te hours in any of the dates so the hours will be "0" as you have shown and also the hours which is less than 8. 

[DataPirate26]

@DataNath I checked the data and the solution is not giving correct result.Can you check once. It is giving hours as 0 in few scenarios where 8 hours has been clocked against that date. 

[DataNath]

@DataPirate26 can you isolate an example of this? Wouldn't expect it to as I'm grouping by [Date] as part of the summarize, in order to get the sum.

In the meantime, I've added another condition to the final filter which checks that the month of [Date] = 7 i.e. July. You can see that even if I select a date range far beyond that on both sides, only the July data is returned for those with 0 hours and any below 8.

[DataPirate26]

@DataNath This is becoming a hardcoded solution. it needs to be dynamically changed So that any data I fetch it give me the results accordingly. 

Example: Suppose the working days for this month till date is 1st, 2nd Sept,  5th-9th Sept, 12th and 13th Sept.  (3rd, 4th, 9th, 10th are Weekends and we are not concerned about them). The Dataset will have only weekdays hours clocked. 

Now Suppose an employee XYZ misses out to clock time on 6th and 7th Sept. In that case there will be no hours clocked for that employee on 6th and 7th even if that is a working day. The rows for date 6th and 7th will be itself missing as he haven't clocked anytime. We need to track these scenarios where the date itself is missing even if it is a working day and not weekends. So that means we will have to compare Each working date of the month with the dates that are there in the dataset. 

Let me know if this helps. Also it should be dynamic. So if today we are checking for suppose 1st - 10th Sept. Next month we might check for 5th - 20th October. Also we might check it for 3 months. Start Date: 5th July , End date: 5th Sept. 

[DataPirate26]

@DataNath I have attached a screenshot below. If you see for this particular employee 27th and 28th July no hours have been clocked. but these two dates were working days(not weekends). So that means she has missed out on this date to clock the hours and we need to Track these 2 dates and Put the Hours as 0. Also along with this we need to pull up the dates where the Total hours clocked is less than 8.

So these two conditions check is required. 

See if you can update the workflow now based on your understanding. 

[DataNath]

Hey @DataPirate26, this is becoming a pretty interesting build. In this latest iteration I've added in another stream which takes the min/max dates from the selection, generates all months in between and then applies this to the final filter to only maintain data for the selected date range and should therefore be dynamic to the range rather than fixed to July as I built previously. If you can isolate an actual row within the Results when you run this where you're finding an issue with 0 hours where there should be billed hours then that'd be best - the example you have provided above is for a staff member not in the sample you gave originally.