We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Creating a Dynamic Date Filter and Flag the Missouts

DataPirate26
9 - Comet

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. 

21 REPLIES 21
DataPirate26
9 - Comet

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

DataNath
16 - Nebula

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:

 

DataNath_3-1663072458750.png

 

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:

 

DataNath_2-1663072435298.png

 

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:

 

DataNath_0-1663072322864.png

 

They are still there with the correctly logged number of hours, as expected:

 

DataNath_4-1663072706684.png

DataPirate26
9 - Comet

@DataNath The results are coming perfect. Maybe i was checking in a wrong way. Thanx for your assistance. I will re-check once and maybe i can post here if there i find any concerns. 

DataPirate26
9 - Comet

@DataNath Can you once help me to understand what the below Filter is checking exactly ? 

DataNath
16 - Nebula

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

DataPirate26
9 - Comet

@DataNath Thanx for the suggestion. 

DataPirate26
9 - Comet

@DataNath Is there any way by which I can write the Date Range that I am selecting through the Analytic App in my output File. 

For example : I am selecting the date range from Start date: 1st July 2022 , End Date: 31st July 2022. I want this date to be reflected at the top in my output File. Screenshot attached below. 

Note: I am using render tool to write the Output. Not necessary though a normal output tool can also be used. Can you help me with this ? 

DataNath
16 - Nebula

@DataPirate26 have attached the most recent iteration which will give outputs like so:

 

DataNath_0-1663145329345.png

 

You may just want to play around with the table/text settings etc. to get the formatting as you wish.

DataPirate26
9 - Comet

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

DataNath
16 - Nebula

@DataPirate26 if you just add another Report Text tool and put in 2 blank lines like so:

 

DataNath_0-1663150263281.png

 

Then union it in as normal, remembering to set the order:

 

DataNath_1-1663150296657.png

 

That'll put the blank rows in between as needed:

 

DataNath_2-1663150329688.png

Labels