topic Re: Show record with max field value using aggregation in QlikView

Show record with max field value using aggregation

mschatt — Mon, 28 Nov 2016 11:28:16 GMT

Hello Experts,

I have situation: I want to find record with max value of field "prediction sum". I am using the aggregation: aggr(max(prediction_sum),prediction_for). Field "prediction_for" has one value across the table. After aggregation I have one record, but value of the field "probability" is different than in source table. Why? (pic below)

Thanks in advance

Re: Show record with max field value using aggregation

tresB — Mon, 28 Nov 2016 11:38:31 GMT

"probability" is a field or you are using some expression to create it?

Re: Show record with max field value using aggregation

MK_QSL — Mon, 28 Nov 2016 11:38:44 GMT

May be

FirstSortedValue(probability, aggr(max(prediction_sum),prediction_for)

Re: Show record with max field value using aggregation

mschatt — Mon, 28 Nov 2016 11:42:23 GMT

Probability is a field. I am loading it form datasource.

Re: Show record with max field value using aggregation

tresB — Mon, 28 Nov 2016 11:44:13 GMT

Try in your chart like:

FirstSortedValue( Aggr(probability, prediction_for), -aggr(prediction_sum, prediction_for))

if this doesn't work, try to share your sample qvw.

Re: Show record with max field value using aggregation

mschatt — Mon, 28 Nov 2016 12:14:22 GMT

In attachment is an example qvw. I thought that when I use aggregation it will show the one record with max field and other fields related to this value. Something is missing?

Re: Show record with max field value using aggregation

tresB — Mon, 28 Nov 2016 12:22:12 GMT

Use calculated dimension like:

=Aggr(FirstSortedValue( probability, -prediction_sum), prediction_for)