How can I select the item based on the group threshold?

Hi,

I have the following data. I'm trying to select the number based on a group threshold of 80. So I basically have to select the highest number within the group to make the group total to be under the group threshold of 80. Is there a way to do that with the formula tool? or other tools?

Group	Number	Select	Not Select	Each Group's Total (currently)
A	10			150
A	30
A	40
A	50
A	20
B	90			135
B	10
B	15
B	20
C	40			210
C	50
C	20
C	100

Thanks for your help!! Much appreciated.

Datasets

Developer Tools

Custom Tools

Accepted answers

tcroberts

Sorry for taking so long. I've got a couple of solutions for you.

If the order that the records are in currently isnt important, than you could sort by Group, then by Number (ascending), and use the a RunningTotal Tool, followed by a Formula Tool with the following expression

IF [RunTot_Number] - [Number] >= 80
THEN "YES"
ELSE "NO"
ENDIF

Spoiler

Ive attched a Numeric UpDown Input so that you could change the threshold dynamically, but hard-coding it would work just as well.

If the order of the records is important, you could add a RecordID Tool first, and then sort by the RecordID afterwards.

Let me know if this solution works for you. I have a slightly more complicated iterative macro-based version, but I think this one should suffice.

Cheers!

flag_group_thresh_1.yxwz

All comments

asilva

tcroberts

I'm working on a sample workflow to solve this right now, but I just want to clarify:

You're looking to have each group be under some group threshold (in your case, 80). To do so, you want to remove the highest number from each group if necessary.

Is my understanding correct? What happens if removing a single number does not get the group below 80 (as is the case with Group A), would you then remove the second largest number?

Cheers!

calvinwong

Thank you so much. I want to basically put a "Yes" on the highest number (not necessary removing it) under the Select column if the group total is over the threshold. And yes. In Group A's case, then I will need to put a Yes on both 50 and 40 under the select column. Hopefully that make sense.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703