topic Re: Show best selling ColorCode of article Total in QlikView

Show best selling ColorCode of article Total

AndersGS — Fri, 15 Nov 2024 23:50:17 GMT

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

Re: Show best selling ColorCode of article Total

MayilVahanan — Fri, 09 Oct 2020 10:30:59 GMT

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

Re: Show best selling ColorCode of article Total

Taoufiq_Zarra — Fri, 09 Oct 2020 10:37:24 GMT

@AndersGS If I understood correctly

for this input :

straight table :

dimension Article

Expression 1 for example Sum(Sales)

new Expression :

=FirstSortedValue(ColorCode,-Sales)

output:

Re: Show best selling ColorCode of article Total

AndersGS — Fri, 09 Oct 2020 10:50:33 GMT

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

Re: Show best selling ColorCode of article Total

AndersGS — Fri, 09 Oct 2020 11:18:47 GMT

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

Re: Show best selling ColorCode of article Total

AndersGS — Tue, 20 Oct 2020 07:22:37 GMT

Hi again,

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

Many thanks

Anders