Max Value using Aggr Function

[#1]

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

[kaushiknsolanki]

Hi,

Try this Expression.

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

Regards,

Kaushik Solanki

[sunny_talwar]

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)

[Digvijay_Singh]

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.

[rajaxavier]

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;

[Larcombes]

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