Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Jester
Contributor II
Contributor II

Show Best Product of Type in Table

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:

TypeProductUnits
PepperBell50
PepperJalepeno20
PepperSerrano15
PepperPoblano17
SquashZucchini10
SquashYellow12
SquashButternut5
SquashSpaghetti7

 

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

Type#1 ProductTotal Units
PepperBell102
SquashYellow34

 

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.

Labels (1)
1 Solution

Accepted Solutions
dilipranjith
Partner
Partner

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

View solution in original post

1 Reply
dilipranjith
Partner
Partner

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

View solution in original post