Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
have a look at the attachment
you can use firstsortedvalue function
no need to do anything in the script
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.
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
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.
Hi ,
i attached u r QVW with solution u can see that