Show Best Product of Type in Table

[Matt_Jester]

I have a need to show the total sales for a given type of product in a table while also showing the best selling single product of that type.

Say I have the following data:

Type	Product	Units
Pepper	Bell	50
Pepper	Jalepeno	20
Pepper	Serrano	15
Pepper	Poblano	17
Squash	Zucchini	10
Squash	Yellow	12
Squash	Butternut	5
Squash	Spaghetti	7

In Qlik I need to have a table that looks like this:

Type	#1 Product	Total Units
Pepper	Bell	102
Squash	Yellow	34

I have tried using the following as a dimension.
=if( Aggr( sum(Units), Product) = max( Aggr( Sum(Units), Product), Product)

If I filter to one Type and replace "max( Aggr( Sum(Units), Product), Product)" with 50, I get the correct result under "#1 Product", and that portion evaluates correctly in the table on it's own.

Any help is appreciated.

[dplr-rn · Accepted solution]

Add a measure like below
firstsortedvalue(Product,-aggr(Sum(Units), Product))
this will give you the top product

as an explanation the aggr part creates a virtual table of product and sum of units. the - is needed sort and get the highest value
another e.g. for aggr
Max(aggr(Sum(Units), Product)) will return top sale amount among products i.e. 50