Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
@AndersGS If I understood correctly
for this input :
straight table :
dimension Article
Expression 1 for example Sum(Sales)
new Expression :
=FirstSortedValue(ColorCode,-Sales)
output:
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
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
Hi again,
Sorry for the delay in checking back on this, I retried your solution today and it worked!
Many thanks
Anders