Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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...
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...
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!
Thank you much for your solution and explanation! This makes sense now