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.
Month | Customer code | Mat Code | Volume | Value |
201801 | AA | CC | 200 | 809.82 |
201802 | AA | CC | 100 | 404.94 |
201803 | AA | CC | 13,620 | 44,793.65 |
201804 | AA | CC | 24,000 | 88,099.69 |
201805 | AA | CC | 2,100 | 7,743.08 |
201806 | AA | CC | 17,357 | 63,634.82 |
201807 | AA | CC | 30 | 40 |
201808 | AA | CC | 10 | 20 |
201809 | AA | BB | 100 | 200 |
201810 | AA | CC | 20 | 30 |
201811 | AA | BB | 50 | 100 |
201812 | AA | BB | 200 | 400 |
Regards, Ravi
Solved! Go to Solution.
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)