Looking to get the first N elements from different elements of data

[lliberm01]

Attached is sample data to illustrate my issue with both sample data and solution.

I am looking to get the highest 3 from "A group", 3 from "B group", 2 from "C group" (blue in solution) and then the top 2 from any group that is left over (green in solution).

This needs to be identified (flagged) by year (2 groups of these top N amounts) and also by both quantity and amount separately.  See solution tab for clarity.

Thanks in advance as I have been struggling with this for too long now.

[sparksun]

Could you confirm if the green part shown in the solution worksheet is consisitent with your described logic: “ the top 2 from any group that is left over”? Or could you further clarify your logic?

[lliberm01]

yes that was to note the top 2 that is left over among any group but for each year.

[Yoshiro_Fujimori]

Hi @lliberm01 ,

I followed your instruction but not match with your expected result.

I hope this may help.

(I worked on [Quantity] only, but you can apply the same logic to [Amount].)

[Qiu]

@lliberm01 
It turns out to be a big flow though the logic is not difficult. 😁

[lliberm01]

Forgot to mention this but the top 3 and top 2 are dynamic amounts (multiplying two other variables to get to this).  Since you can't seemingly use a formula in the sample tool to create the N amounts, is there another way around this?

[lliberm01 · Accepted solution]

I think i figured this out.  If you break the workflow out into two sorted elements of quantity and amount, you can rank everything with multirow +1 for each and take the top 3 (dynamic) amounts in a formula tool by taking all that are less than or equal to the N variable (3).  You can then break out whats left over in a filter and rerank with multitool and do the same process (top 2 [variable driven]).  Once that is done, you can join everything up.  Thanks.  Just wish there was the ability to use a formula in the sample tool and this would all be short cutted.