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

Filter by highest fiscal year and highest fiscal month related to that fiscal year

eb7
6 - Meteoroid

Hi community,

 

I hope you can help me set up an automated filter for my workflow which is updated daily.

 

My data is processed daily which adds current FY 2021 and the current and prior FM (FM 2 and FM 3). My database looks as follows:

FYFMSales
2020115
2020216
2020717
2021117
2021218
2021322

 

If I run my Alteryx WF then FY 2021 and FM 2 and FM 3 will result in duplicated data. Thus, I want an automated filter in my WF to remove the highest FY (2021) and to this year related two highest FM (FM 2 and FM 3). It should not touch/filter any FM related to FY 2020 (FM 7 and FM 2 from FY2020 should not be filtered out).

 

Result wanted in Alteryx WF: Filter out FY2021 and FM2 and FM3 from dataset (last two rows in table above), then load FY2021 and FM2 and FM3. It should look like this after filtering:

FYFMSales
2020115
2020216
2020717
2021117

 

Thank you for your support!

11 REPLIES 11
DataNath
17 - Castor

Hey @eb7 - I’m guessing this needs to be dynamic and the live data/WF is way larger? If not then you could of course just use a sample tool and select the ‘First N Rows’ where N = 4. If not, I’ll build something out when I hop on my laptop if nobody is else has got to you with a solution!

 

EDIT: Certainly not the prettiest, but seems to get the job done.

 

DataNath_0-1652298180706.png

 

terry10
11 - Bolide

@eb7 ,

 

You can dynamically filter the data as you described using a pair of join tools. :)  Workflow attached.

 

terry10_0-1652322602099.png

 

grazitti_sapna
17 - Castor

Hi @eb7 ,

 

Other than the methods mentioned above by @terry10  and @DataNath  I have mentioned 2 more methods. Please refer to the screenshot and the attachment below. If it helps please mark my post and like it!

 

grazitti_sapna_0-1652333295363.png

 

 

Thanks!

Sapna Gupta
eb7
6 - Meteoroid

Hi DataNash,

 

yes, the WF is way lager and yes it must be dynamic as fiscal months progress.

 

I will add this to my WF and do more testings.

 

Thank you very much!

 

Regards

eb7
6 - Meteoroid

Hi terry10,

 

Your solution gives me more ideas for other WF. Thank you for sharing!

 

Regards 

eb7
6 - Meteoroid

Hi grazitti_sapna,

 

thank you for sharing your methods. These are useful in general. I did not mention it but it should be dynamic. I will use this for other WF! Many thanks!

 

Regards

grazitti_sapna
17 - Castor

Hi @eb7 ,

 

I have checked few scenarios and my workflow seems to work dynamically. Please refer to the screenshot below:

Input

grazitti_sapna_0-1652350142770.png


Output

grazitti_sapna_1-1652350182669.png


Thanks!

Sapna Gupta
eb7
6 - Meteoroid

Hi Sapna,

 

when testing by adding FM 1, 2, 3, 4, 5 then only FM 1 is left in the data base after filtering (I guess due to Min_Month step?). So, four months are filtered out. But your models are great in general. Thank you!

 

Regards

grazitti_sapna
17 - Castor

@eb7  Thanks for clarifying. Appreciate it!

Sapna Gupta
Labels