Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max Value using Aggr Function

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

5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this Expression.

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

Is this what you are looking for?

Capture.PNG

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.

Capture.JPG

rajaxavier
Contributor
Contributor

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
Contributor
Contributor

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