Help with filtering records that lie between first day and last day of month

Hi All,

I have to filter out records from a data set to capture only those that occurred within the month. given that some months are 28, 29, 30 and 31, how do I write something that will filter out all records that aren't within a given month. I have Designer Desktop only.

To add complexity, the reporting is in arrears, so I will be running March data in April. - being able to select which month is okay too, while manual, it is one setting which isn't terrible.

Additional complexity, as a variation on the Month, I need to run quarterly as well for the records within the quarter.

I have no experience with macros whatsoever so if there is a way that this can be accomplished without manual intervention (monthly) that would be great. I am using a directory file read in to select the most current file in the directory based upon creation date as the files will be named with a date suffix; however, that has been known to change format thus the creation date in descending sort order is used

I'd like to share the file; however, there is company info so the best I can do is describe the condition I am trying to solve for. I appreciate any assistance or guidance.

Thanks!

Date Time

Dynamic Processing

Accepted answers

All comments

binu_acs

@kimc745 use the DateTimeFirstofMonth() an DateTimeLAstOfMonth() functions to calculate first and last of the month

Date_260124.yxmd

kimc745

Thank you! Is it possible to have this look at current month - 1 so it can auto-sense month-to-month without me having to change a date or filter record sets. The source data has records for more than one month and we do reports one month in arrears. And can that logic be applied to quarters are well? I'll start to play around with it since what you provided gives me a boost on it. Thank you so much! @binu_acs

aatalai

@kimc745 could this post help with your previous month query?

kimc745

@aatalai Thanks for the post. Attempting the solutions, as I look through the data, I found another challenge that the requestor didn't mention. This file has multiple years as well as multiple months. Perhaps your solution still works but I am struggling with date/time to look at current date, go get prior month records that fall into that month and years records. And as mentioned, then I have to go back and get it for quarter and account for change of year for 4th quarter. I sure wish I had training in this beyond what is free. :-( Alas, I am super thankful for all in the community. With everyone's help I can usually piece together a solution.

aatalai

@kimc745 team work makes the dream work

kimc745

@binu_acs @aatalai

I am going to accept solution for prior month and if I have issues open a new post. Thank you both so much. I was able to combine the solutions offered by both of you and get my filtered set. For some reason, when I tried to convert the report date on the incoming data set to Date, Alteryx gave me an error. So after I used the Date/Time Parse tool to make it date format. Now I come to where you helped. I created variables for first and last (@Aatalai) and then used them in the formula by @binu_acs and that filtered the data set. Now I just have to format it with the / instead of - between dates to provide the output formatted as they like. I believe I know how to do that but if either of you do, happy to accept help. Now I just have to finish the filtering criteria and logic needed before I tackle previous quarter. Very much appreciated!

kimc745

@binu_acs @aatalai - Your recommendations seemed to have solved my issues including on the quarter and arrears providing I executed them properly. I have another question for another post but wanted to again thank you both very much for your help!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703