I have a table:
To get SUM(sales) group by Product I am using : Aggr(Sum(Unitsales), Product)
To get top most Product's Customers: FirstSortedValue(Customer, -Aggr(Sum(UnitSales), Product))
I got result as "A.Astrida"
Since top most Product AA is associated with 3 customers, on what basis I am getting "A.Astrida" ?
Why not B.Astrida or Canutility ?
Any help on this ??
Thanks,
Bharath
Hi!
If you use Aggr(sum(UnitSales),Product)), you will get only one value of UnitSales by each one Product.
If you want to get a table with Product Dimmention and only one Expression with the top UnitSales Customer:
Dimmention: Product
Expression: FirstSortedValue(Customer,-aggr(sum(UnitSales),Customer,Product))
Hi Sebastian Pereira,
Thanks for your response.
I got it, but i just want to know,
Why I got A.Astrida for Expression : FirstSortedValue(Customer, -Aggr(Sum(UnitSales), Product))
why not the other 2 Customers !!
OK.Thanks Anyway!
if you get only one value for each product (with aggr), you get the same value for each Customer. So, FirstSortedValue gives your anyone.
If it resolved your question, please mark as answered !
For others, You can try like this?
FirstSortedValue(Customer, -Aggr(Sum(UnitSales), Product),1)
FirstSortedValue(Customer, -Aggr(Sum(UnitSales), Product),2)
FirstSortedValue(Customer, -Aggr(Sum(UnitSales), Product),3)
OR
Concat(FirstSortedValue(Customer, -Aggr(Sum(UnitSales), Product)), ',')
Hi Anil Babu,
Thanks for your reply, but i am getting error when trying this expression. I am trying in Qliksense November 2017 Version
May be try this
Concat(Aggr(If(Sum(TOTAL <Product> UnitSales) = Max(TOTAL Aggr(Sum(TOTAL <Product> UnitSales), Product, Customer)), Customer), Product, Customer), ', ')
HI Bharath,
For your desired result, consider Anil Babu's answer. You aren't getting the desired result as the aggr function is giving one value, which is again been ranked on the basis of Product which effectively remains same, as you have already grouped by Product, so in any case, you'll be getting only one value.
Hope it helps.
Regards,
Gagan