topic Re: Text value for aggregation function in QlikView

Text value for aggregation function

sculptorlv — Fri, 11 Sep 2015 09:54:55 GMT

Hello, Please help in a simple question!!

I have the following table

Client_Number | Agetn_Name_in_STA | STA_Money_2015

2 Alex 14

2 Alex 1

2 Michel 11

3 Alex 3

I use an aggregation function:

=aggr(MAX(aggr(SUM(STA_Money_2015), Client_Number, Agetn_Name_in_STA )), Client_Number)

As the result, I have MAX sum value in the column.

How can I get no the MAX sum value, but the Agetn_Name_in_STA value, which have this MAX sum value??

Thank you in advance!

Re: Text value for aggregation function

MK_QSL — Fri, 11 Sep 2015 09:57:14 GMT

Confusing description.. Can you please elaborate little more ?

Re: Text value for aggregation function

sunny_talwar — Fri, 11 Sep 2015 09:58:15 GMT

Try this:

=FirstSortedValue(Agetn_Name_in_STA, -Aggr(Max(Aggr(Sum(STA_Money_2015), Client_Number, Agetn_Name_in_STA )), Client_Number))

Re: Text value for aggregation function

parthakk — Fri, 11 Sep 2015 10:03:18 GMT

Hi,

Coludn't understand question. You want Agetn_Name_in_STA associated for max STA_Money_2015?

Thanks,

Partha K

Re: Text value for aggregation function

sculptorlv — Fri, 11 Sep 2015 10:04:27 GMT

Thanks for help. But didn't work for me.

I'll try to expand the question.

I have several sales agents, which works with clients. I need to understand for each client, which agent have maximums sales.

Your formula set the FIRST agent in the row.

Re: Text value for aggregation function

sunny_talwar — Fri, 11 Sep 2015 10:09:00 GMT

So this you need in a straight table:

Try this may be

Dimension: Client_Number

Expression:

=FirstSortedValue(Agetn_Name_in_STA, -Aggr(Sum(STA_Money_2015), Client_Number, Agetn_Name_in_STA )))

Re: Text value for aggregation function

sculptorlv — Fri, 11 Sep 2015 10:12:40 GMT

Thank YOU!

This works fine!!

Dimension: Client_Number

Expression:

=FirstSortedValue(Agetn_Name_in_STA, -Aggr(Sum(STA_Money_2015), Client_Number, Agetn_Name_in_STA ))

Re: Text value for aggregation function

sunny_talwar — Fri, 11 Sep 2015 10:18:19 GMT

Awesome

I am glad it worked because I was not completely confident it would work, but just wanted you to give it a shot.

Best,

Sunny