Aggr with FirstSortedValue

[Anonymous]

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

[sebastiandperei]

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))

[Anonymous]

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!

[Anil_Babu_Samineni]

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)), ',')

[sunny_talwar · Accepted solution]

May be try this

Concat(Aggr(If(Sum(TOTAL <Product> UnitSales) = Max(TOTAL Aggr(Sum(TOTAL <Product> UnitSales), Product, Customer)), Customer), Product, Customer), ', ')

[sebastiandperei]

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 !

[gagan_bhasin]

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

[Anonymous]

Thanks Sunny Talwar !!

[Anonymous]

Hi Anil Babu,

Thanks for your reply, but i am getting error when trying this expression. I am trying in Qliksense November 2017 Version