Finding the group with max value

[perla_chirag]

Hi all,

I have attached Sample data (QVW and Excel file).

There are 4 columns (ID, Group, Value, Date)

The overall Max from the Value data points i get from a simple Max(Value) expression in  a text object

Now I need to find the Group which corresponds to this Max Value, and similarly the Date on which the Max Value occurred.

The QVW that I have provided is loaded with the sample data, and I have created text objects where you'll can show me the expression to use to get the Group with Max value and Date with Max value.

Thanks in advance

[lironbaram]

have a look at the attachment

you can use firstsortedvalue function

no need to do anything in the script

[rubenmarin · Accepted solution]

Hi Chirag, one easy option:

for group: =FirstSortedValue(Group, -Value)

for date: =FirstSortedValue(Date, -Value)

But it can fail if for the max value there are more than one group and/or date. What has to show when there is a tie in max value? something like?:

for group: =Concat(DISTINCT {<Value={'$(=Max(Value))'}>} Group, ', ')

for date: =Concat(DISTINCT {<Value={'$(=Max(Value))'}>} Date, ', ')

BTW, very good format of your post: well explained and guided samples.

[perla_chirag]

Thanks It works! Just needed to make a small change in the expression for Date

"=FirstSortedValue(Date,-aggr(max(Value),Group))"

changed to

"=FirstSortedValue(Date,-aggr(max(Value),Date))"

Thanks for your help on this Liron

[perla_chirag]

Perfect Rubin! The easy option is really elegant.

And your solution for tackling more than 1 possible groups/dates works like a charm.

Thanks for the help

And yes, I tried to make my question as simple to understand as possible, and the sample qvw file was to make my helper's life easy in answering according to what i am searching for Thanks again Rubin.

[#6]

Hi ,

i attached u r  QVW with solution u can see that