Show best selling ColorCode of article Total

[AndersGS]

Hi,

In a QlikView straight table I'm showing the top 5 best selling articles based on it's total Sales value yesterday.
In a separate column I want to display the top 1 selling colorcode within each of the top 5 Articles. 

The fields are : 'Article', 'ColorCode' and the variable 'vSales_LD' 

My Goal:

Article	$(vSales_LD)	Top 1 selling ColorCode
07409	200 000	99
07203	100 000	92
05103	50 000	33
07712	25 000	94
07412	10 000	96

Table with all Articles with each ColorCode sold yesterday:

Article	ColorCode	$(vSales_LD)
07409	99	150 000
07203	92	75 000
07409	33	50 000
05103	99	40 000
07203	26	25 000
07712	94	20 000
05103	92	10 000
07412	96	8 000
07712	26	5 000
07412	99	2 000

We are running QlikView 11.20.13707.0

Thanks a lot
Anders

[MayilVahanan · Accepted solution]

Hi Anders,

Try like below in chart expression

FirstSortedValue(ColorCode, -Aggr($(vSales_LD), Article, ColorCode))

It will gives the ColorCode which contains high sales for that articles

[Taoufiq_Zarra]

@AndersGS  If I understood correctly

for this input :

straight table :

dimension Article

Expression 1 for example Sum(Sales)

new Expression :

=FirstSortedValue(ColorCode,-Sales)

output:

[AndersGS]

Hi Taoufiq,

Thanks for your quick reply. I'm trying with =FirstSortedValue(ColorCode,-$(vSales_LD)) but It throws an error:
"Nested aggregation not allowed".  The variable 'vSales_LD'  is Sum ({$<#Date_O={1},%Type={'O','C','I','IC'},_LD={1}>} mcAmount / EXC.RATE).

Regards
Anders

[AndersGS]

Hi mayilvahanan,

Thanks for your quick reply. This expression returns "Errors in expression" unfortunately, do we miss any parentheses or will this not work with my variable (please see my reply to Taoufiq for more details)?

Regards
Anders

[AndersGS]

Hi again, 

Sorry for the delay in checking back on this, I retried your solution today and it worked! 

Many thanks

Anders