Filter line items for last three months

Question

Hello Experts,

How can I put a filter in workflow so that only line items for end user code and material code for the last three months are filtered/ picked.  I have the data for the entire 2018 in below tabular format.

Eg:  For customer AA and Mat code as BB, line items for fiscal period  201809, 201811, 201812 should be filtered.

Similarly, for customer AA and Mat code as CC, line items for the month 201807, 201808 and 201810 should be filtered.

Tried using multi row formula but could not achieve the desired results.

MonthCustomer codeMat CodeVolumeValue201801AACC200809.82201802AACC100404.94201803AACC13,62044,793.65201804AACC24,00088,099.69201805AACC2,1007,743.08201806AACC17,35763,634.82201807AACC3040201808AACC1020201809AABB100200201810AACC2030201811AABB50100201812AABB200400

Regards, Ravi

JoBen · Accepted Answer

Hi @Ravy12, could you use the sort and sample tool in combination? See below.

estherb47 · Accepted Answer

Hi @Ravy12

I'd take @JoBen 's approach of a sort with a sample tool. Easiest way to go. If you also need to include the customer code, just tick that off in the sample tool

But, if you'd like to play with the multi-row formula tool and a filter, try this approach

The summarize tool counts how many records are for each Mat Code. Join that information back to the original data, and then sort. The multi-row formula tool gives a new record ID within each Mat Code, starting with 1 and ending with the last (3 for BB, 9 for CC). Then filter out where the count minus the row ID is less than 3 - this gives the last three.

You can tweak the sort and the Multi-row tool to account for the Customer ID as well (just add Customer ID as the first sorting level, and in the Multi-row tool, group by customer ID and by Mat Code)