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

[eb7]

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:

FY	FM	Sales
2020	1	15
2020	2	16
2020	7	17
2021	1	17
2021	2	18
2021	3	22

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:

FY	FM	Sales
2020	1	15
2020	2	16
2020	7	17
2021	1	17

Thank you for your support!

[DataNath · Accepted solution]

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.

[terry10]

@eb7 ,

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

[grazitti_sapna]

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!

Thanks!

[eb7]

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]

Hi terry10,

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

Regards 

[eb7]

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]

Hi @eb7 ,

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

Input

Output

Thanks!

[eb7]

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]

@eb7  Thanks for clarifying. Appreciate it!