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!

[eb7]

Hi @DataNath

I have built in your solution in my WF but i noticed that I have hundred of rows for each fiscal months. Said that, the filter with:

[Month ID] != [Max_Month ID]
AND
[Month ID] != ([Max_Month ID]-1)

filters out the last two rows instead of all entries regarding the last two fiscal months. Any idea here?

2nd issue: When Fiscal year changes and there will be only one fiscal month available. Will the formula cause trouble?:

[Month ID] != [Max_Month ID]
AND
[Month ID] != ([Max_Month ID]-1)

Best Regards

[DataNath]

@eb7 my original understanding was, if it's the most recent year in the data set, you want to filter out the most recent month & the one before it (max month & max month-1) - is this not the case? If so, what exactly do you need to be removed?

I don't understand what you mean by 'regarding the last two months' either, sorry.

If you could provide a bit of a larger sample size and the expected outcome then that would help to build out the amended solution too, cheers!

As to your second point - I had overlooked that but it's very easy to avoid. We can just add in another step which checks whether or not the amount of fiscal months is > 1.