Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alandilworth
Partner - Contributor III
Partner - Contributor III

Finding max values in groupings

I have the table below, and I am trying to return the 'GaltOwner', 'Month', and 'SUM(WAC)' that has the highest value in the 'SUM(WAC)' column for each 'GaltOwner' grouping.   I'm having a hard time figuring out how to do this logically.  The only way I can think of involves somehow placing the values in an array and iterating over it.  Any insight is greatly appreciated!  I've been stuck for a few hours now.

For example, the results from the table below should be:

MDX3 20-Jul 18690.00
Milliya 20-Nov 29480.00
MLWoodall 19-Dec 33480.00

 

 

1 Solution

Accepted Solutions
PradeepK
Creator II
Creator II

First Point: You are using 2 dimension, So you need to use both for grouping. Then find the Max by ignoring irrelevant dimensions; in your case only consider <GaltOwner> for Max calculation.

 Second Point: You want to have only rows that satisfies Max data condition. So you need to create calculated dimension as below and disable 'Include null values' option.

<if you add more dimension in table; Make sure to add it accordingly in expression>

 

 

Aggr(
  if(	Max(Total <GaltOwner> Aggr(Sum(WAC),GaltOwner, Month)) = Sum(WAC)
     , GaltOwner)
   , GaltOwner , Month
)

 

 

Although, I don't recommend using Aggr  <Note you can always move such logic in load Script>

 

I have tested it on different data set: See below screenshots 

 

 

Aggr(
  if(Max( Total <Region> Aggr(Sum([Total Sale Amount]),Region, SalesRepresentative)) 
  		= Sum([Total Sale Amount]), Region)
   , Region , SalesRepresentative
)

 

 

PradeepK_0-1616095782796.png

 

Hope this helps!

View solution in original post

4 Replies
Or
MVP
MVP

Something along the lines of (can't test, but this is the general idea):

if(Rank(Sum({<Month=>} WAC))=1,Sum(WAC))

You can search up similar threads about finding the largest [Dimension1] for each [Dimension2], e.g. https://community.qlik.com/t5/QlikView-App-Dev/Aggregation-finding-the-biggest-customer-for-each-pro...

 

alandilworth
Partner - Contributor III
Partner - Contributor III
Author

I appreciate the response!  This does not seem to work though and also does not consider the grouping under column 'GaltOwner'. 

I have tried the method from here as well with no success:  https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-find-Avg-and-Max-of-a-calculated-field-in-Qli...

PradeepK
Creator II
Creator II

First Point: You are using 2 dimension, So you need to use both for grouping. Then find the Max by ignoring irrelevant dimensions; in your case only consider <GaltOwner> for Max calculation.

 Second Point: You want to have only rows that satisfies Max data condition. So you need to create calculated dimension as below and disable 'Include null values' option.

<if you add more dimension in table; Make sure to add it accordingly in expression>

 

 

Aggr(
  if(	Max(Total <GaltOwner> Aggr(Sum(WAC),GaltOwner, Month)) = Sum(WAC)
     , GaltOwner)
   , GaltOwner , Month
)

 

 

Although, I don't recommend using Aggr  <Note you can always move such logic in load Script>

 

I have tested it on different data set: See below screenshots 

 

 

Aggr(
  if(Max( Total <Region> Aggr(Sum([Total Sale Amount]),Region, SalesRepresentative)) 
  		= Sum([Total Sale Amount]), Region)
   , Region , SalesRepresentative
)

 

 

PradeepK_0-1616095782796.png

 

Hope this helps!

alandilworth
Partner - Contributor III
Partner - Contributor III
Author

Thank you much for your solution and explanation!  This makes sense now