Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bharath_kumar1
New Contributor II

Aggr with FirstSortedValue

I have a table:

pic.JPG

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

1 Solution

Accepted Solutions

Re: Aggr with FirstSortedValue

May be try this

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

8 Replies
sebastiandperei
Valued Contributor

Re: Aggr with FirstSortedValue

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

bharath_kumar1
New Contributor II

Re: Aggr with FirstSortedValue

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!


Re: Aggr with FirstSortedValue

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

Re: Aggr with FirstSortedValue

May be try this

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

sebastiandperei
Valued Contributor

Re: Aggr with FirstSortedValue

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
New Contributor II

Re: Aggr with FirstSortedValue

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

bharath_kumar1
New Contributor II

Re: Aggr with FirstSortedValue

Thanks Sunny Talwar !!

bharath_kumar1
New Contributor II

Re: Aggr with FirstSortedValue

Hi Anil Babu,

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

Community Browser