# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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
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
)``````

Hope this helps!

4 Replies
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...

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

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
)``````

Hope this helps!

Partner - Contributor III
Author

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

Tags
Community Browser