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

[calvinwong]

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.

[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.

[tcroberts · Accepted solution]

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!

[calvinwong]

Thank you tcroberts. This works. But my last question is, I feel like the analytic app doesn't work for some reason after I update the number to 110. Let me know what your thoughts are.

[tcroberts]

Hmm... it doesn't work at all, or it gives the wrong answer?

I'll have to take a closer look tomorrow morning, but it could be because I misconfigured the update tool. Check that its replacing a specific string, that string being: 80

If not I'll let you know when i've had a chance to dig into it

[calvinwong]

When I run the analytic app and change it to 110. It basically gives me the same answer as the 80 one. Thanks for all the help. You have been really helpful. :)

[tcroberts]

Yea that sounds like I configured the input incorrectly. I'll upload a fixed version for you tomorrow morning

[tcroberts]

Hi @calvinwong,

It was indeed the configuration of the update tool. I've changed it to replace a specific string: 80, so what will happen is whenever the input value changes, this 80 in the base expression is replaced with the value from the input. It should work now for values in the range of 0-250, otherwise you'll have to update the "Maximum" configuration in the Numeric UpDown Tool.

Cheers!