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 line items for last three months

Ravy12
7 - Meteor

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 CodeVolumeValue
201801AACC200809.82
201802AACC100404.94
201803AACC13,62044,793.65
201804AACC24,00088,099.69
201805AACC2,1007,743.08
201806AACC17,35763,634.82
201807AACC3040
201808AACC1020
201809AABB100200
201810AACC2030
201811AABB50100
201812AABB200400

 

Regards, Ravi

2 REPLIES 2
JoBen
11 - Bolide

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

 

Help1.PNG

estherb47
15 - Aurora
15 - Aurora

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


image.pngThe 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)

Labels