Filter rows based on evaluation frequency

Question

Hello All,

I've created a alteryx workflow which is generating rows based on the activation and decommission dates mentioned in the datasets.

Now  I need to add one more functionality where  my data should filter based on the frequency and recent testing month.

So if the evaluation frequency is quarterly, it should only shows 3 months previous and 3 months forward rows based on the recent testing month column (here the filter should only based on the month).

for example - if recent testing month is in  December 2021 then it should show sept 2021 and march 2022

For semi-annual, it should be 6 months window and for annual it should be 12 months.

And if the recent testing month is more then 365 days then it should assign dummy value to the month column

Please let me know if this can be added to my existing workflow.

Regards,

swapsingh

Date generation.yxmd

Test_cap.xlsx

Output_cap.xlsx

BobR · Answer

For the filtering the example I sent lays out an approach. As I said in the last sentence you will probably have to tweak the date math to match exactly what you are looking for. So to get the exact behavior you are looking for adjust the filter I sent. Looks like i put a +1 in for the start date when I probably meant to -1. So just tweak that until you get the behavior you need.

As for the random month requirement. My example didn't address that. I missed that in your original note, but few points on that.
* I would not suggest a random value. It should be a date column so a random value would be misleading in the data. Instead i would make the value null() which generally means the absence of data which sounds like more appropriate.

* to put this in your code you could so something this in a formula tool.

if datetimediff(datetimetoday(), [last_testing_date], "days") > 365 then null()

else [Month]

endif

I don't have alteryx in front of me so there might be a typo, but this is the gist of solving that problem.

Bob

swapsingh2712 · Answer

Hello @BobR

Thank you so much for your response.

I tried to use this logic but it's not giving the correct data.

so if the evaluation frequency is monthly, it should generate the rows normally (column name in month for row generation)

But if it's quarterly, it should only fetch those rows from the datasets which are 3 month apart from the recent testing month

for example if for particular test has recent testing month 2021-09-30

then it should fetch the records where the months column contains 2021-06-30, 2021-12-30,

same goes for annually and semi annually.

And if recent testing date is 365 days apart from the current date time, then it should still fetch the rows as per the quarterly, annually basis but just assign the random values to months columns

if the random values shows up, we'll get to know that the test was not evaluated recently.

Hope this helps

Regards,

Swapsingh2712

Date Generation_1.yxmd

Output_cap_1.yxdb

BobR · Answer

I added a container here with logic. Its probably not exactly your final logic, but you should be able to tweak it.

Yes you could put all this in a custom filter, but I find that is hard to debug and hard for the people that come after you.

So you use formula too to figure out how many months you want to keep from the testing date.

Then you use the datetimeadd() function to create a field when you want the filter to start. and another when to end.

Then I use a filter tool to apply the filter.

Last a select to get rid of the 3 columns we temp created.

Only thing that should need tweaking is if the days in the dates aren't getting you the exact output. Might just want to baseline the calc off the first of the month for example.

Date generation.yxmd