topic Re: Finding the group with max value in QlikView

Finding the group with max value

perla_chirag — Mon, 06 Jul 2015 12:31:16 GMT

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

Re: Finding the group with max value

lironbaram — Mon, 06 Jul 2015 12:35:35 GMT

have a look at the attachment

you can use firstsortedvalue function

no need to do anything in the script

Re: Finding the group with max value

rubenmarin — Mon, 06 Jul 2015 12:41:37 GMT

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.

Re: Finding the group with max value

perla_chirag — Mon, 06 Jul 2015 13:03:19 GMT

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

Re: Finding the group with max value

perla_chirag — Mon, 06 Jul 2015 13:09:13 GMT

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.

Re: Finding the group with max value

Mon, 06 Jul 2015 13:18:02 GMT

Hi ,

i attached u r QVW with solution u can see that