topic Max Value using Aggr Function in QlikView

Max Value using Aggr Function

Wed, 25 Nov 2020 16:16:04 GMT

Hello all ,

In my source file , In each month there are multiple ID's and each ID's has different Sales value .

I am trying to pick only the maximum Sales value in a month.

I used this expression Max(aggr(sum(Value),ID,Product))

The above expression works if i add Month and Product as dimensions and it takes the maximum value of a corresponding month.(Bar Chart)

But when I add ID in the dimension , it doesn't takes the maximum value , Instead it shows all the IDs with all the values.(Straight table)

Could you please help me on this ?

Thanks ,

Siva

Re: Max Value using Aggr Function

kaushiknsolanki — Mon, 07 Mar 2016 10:46:42 GMT

Hi,

Try this Expression.

Sum(aggr(Max(Value),Product))

Regards,

Kaushik Solanki

Re: Max Value using Aggr Function

sunny_talwar — Mon, 07 Mar 2016 11:30:54 GMT

Is this what you are looking for?

Expression:

Sum({<ID = {"=Aggr(Sum(Value), Month, ID) = Aggr(NODISTINCT Max(TOTAL <Month> Aggr(Sum(Value), ID, Month)), Month)"}>}Value)

Re: Max Value using Aggr Function

Digvijay_Singh — Mon, 07 Mar 2016 11:40:46 GMT

The dimensions used in the chart should be equal or less granular than Aggr expression dimensions, chart dimensions will decide how to show Aggr results.

As shown below Chart dimensions are Month, Product, ID(more granular, not desired!) than last Aggr dimension(Month, less granular). Show it calculates expression value once only for each month.

Re: Max Value using Aggr Function

rajaxavier — Mon, 20 Apr 2020 17:52:34 GMT

BACKEND SCRIPT

Tab1:
LOAD
"Year",
Trimester,
"Month",
Value
FROM [lib://data.xlsx]
(ooxml, embedded labels, table is Sheet1);

left Join (Tab1)

Tab2:
load
Trimester,
max(Value) as max
Resident Tab1 group by Trimester;

Re: Max Value using Aggr Function

Larcombes — Wed, 22 Apr 2020 07:06:53 GMT

As mentioned before, when do we use AGGR function, it creates a temporary table. In this case, QlikView has calculated the sale for each Product by Company. From this virtual table we have to identify maximum sales value and it is done by MAX() function prepaidcardstatus