Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Filter rows based on evaluation frequency

swapsingh2712
8 - Asteroid

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

3 REPLIES 3
BobR
8 - Asteroid

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.

swapsingh2712
8 - Asteroid

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

 

 

 

BobR
8 - Asteroid

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

Labels