Have an idea?

Visit Sawtooth Software Feedback to share your ideas on how we can improve our products.

Find label for maximum value in a colum

I have a grid with multiple columns.

Let's say I want to find the row label of the maximum value in Column 1. How do I do this?
asked Oct 27, 2013 by sk90 (140 points)

1 Answer

+1 vote
Let's assume your question (Q1) is a grid with a numeric, rank or constant sum type setup?

Also assume the grid direction is columns.

If you setup a constructed list (SortedList) that uses the same parent list as the rows, it would look like this ...

AddSorted(Q1_c1,0)

This constructed list will capture the sorted descending order rows. So the 1st code stored would be the highest.

To display them on a question page, use the following ...

[%ListLabel(SortedList,1)%] - 1st highest row
[%ListLabel(SortedList,2)%] - 2nd highest row
[%ListLabel(SortedList,3)%] - 3rd highest row
[%ListLabel(SortedList,4)%] - 4th highest row
etc.

Use the SortedList in other questions if you want to display them in the sorted order.

Just tested it out, works fine.
answered Oct 27, 2013 by Paul Moon Platinum (98,670 points)
Thanks so much Paul. This is so helpful!

Is there a way I can get values from other columns as well?

Example.
1. Find maximum value in Column 1
2. Find row label corresponding to that value (using method explained by you above)
3. Find value in the same row in Column 2 - how do I do this?
To get the top row label from other columns is easy. Just create another constructed list that uses AddSorted(Q1_c2,0) for column 2, AddSorted(Q1_c3,0) for column 3, etc. and refer to the new constructed list.

Your other problem of finding the value associated to the highest value in a column is more difficult and requires some Perl programming.

Throw this into a constructed list and you will get your value for the highest rated row value for column 1. The same approach can be used for other columns.

Please note - create a parent list called NumericList. This will store the values allowed in your grid question. So if you allow 1-100, insert them into the list. If you allow 1-20, insert them into the list.

Begin Unverified Perl

 ADD("NumericList",(VALUE("Q1_r".LISTVALUE("SortedList",1)."_c1")));

End Unverified

 
Also consider equal rankings. The AddSorted function will randomly rank equal rated rows.
To display this highest value, simply use ...

[%ListLabel(SortedValueList,1)%]

SortedValueList is the constructed list that contains the Perl code. It uses the parent list - NumericList.
To extract the value from the highest ranked row in column 2, you can use the same Perl code but edit the column number and save it in another constructed list...

Begin Unverified Perl

 ADD("NumericList",(VALUE("Q1_r".LISTVALUE("SortedList",1)."_c2")));

End Unverified
...