topic Finding Max value in QlikView

Finding Max value

jduenyas — Thu, 10 Nov 2011 20:57:13 GMT

Hi all

How do I find which order has the max value?

OrderNo	Amount
ABC	125
BCD	325
ABC	541
DEF	125
DEF	135
ABC	438
BCD	223
EFG	123

The dimension is against a SalesPerson

I thought that Max(Sum(aggr(Amount,OredrNo))) will do but it does not.

Thanks in advance

Josh

Finding Max value

Thu, 10 Nov 2011 21:00:45 GMT

Create an expression :

FirstSortedValue( OrderNo , -Amount )

regards

Finding Max value

jduenyas — Thu, 10 Nov 2011 21:20:50 GMT

I must have messed the question.

I am looking for the Max TOTAL value (sum) of an order not the Order that has the Max Value.

Your answer, for sure, will sure help me elsewhere.

Meanwhile, would you know how to extract the Max value for the orders

Finding Max value

Thu, 10 Nov 2011 21:33:30 GMT

Maybe I miss something

Dimension : OrderNo

Expression : Max(Amount)

Finding Max value

jduenyas — Thu, 10 Nov 2011 21:36:44 GMT

But I do not wish to show OrderNo in the dimension side.

Thanks

Re: Finding Max value

Thu, 10 Nov 2011 21:38:48 GMT

Did you try adding in the Sales person to the Aggr function?

Max(aggr(Sum(Amount),SalesPerson,OrderNo))

Finding Max value

jduenyas — Thu, 10 Nov 2011 21:46:40 GMT

HEY THAT WORKED!

= Max(aggr(sum(Amount),OrderNo))

Re: Finding Max value

ToniKautto — Thu, 10 Nov 2011 21:54:39 GMT

First of all you need to keep in mind that aggr() is defined as show below. This means that it must include an expression and then the dimensions over which the expression is calculated. If you are looking for the max value in your dimensions you simple right something like aggr(max(fieldName), dimension1, dimension2, dimension3)

aggr ( [ distinct | nodistinct ] [{set_expression}]expression {, dimension} )

Secondly you need to keep in mind that aggr will return a set of values, so if the aggregation expression does not return a single value you might need put the aggr() in a sum() for example to get the aggr set total sum.

The final answer to your question depends on what your data model looks like in relation to the chart you are trying to accomplish. You are probabaly loking for a construction like max(aggr(sum(fieldname), dimension1, dimension2, dimension3)) or aggr(max(fieldname), dimension1, dimension2, dimension3))

Finding Max value

jduenyas — Thu, 10 Nov 2011 22:09:08 GMT

Thank you Toni. As per my previous reply to all the formula = Max(aggr(sum(Amount),OrderNo)) works correctly.

Your explanation that aggr() returns a set is valuable information that I should keep in mind.

PS I like your icon (the hat). Creative and unique.

Thanks

Re: Finding Max value

Fri, 27 Mar 2015 13:50:48 GMT

Hi Josh,

FirstSortedValue( OrderNo , -aggr(sum(Amount)) )