Text value for aggregation function

[sculptorlv]

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!

[MK_QSL]

Confusing description.. Can you please elaborate little more ?

[sunny_talwar]

Try this:

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

[parthakk]

Hi,

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

Thanks,

Partha K

[sculptorlv]

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.

[sunny_talwar · Accepted solution]

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

[sculptorlv]

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

[sunny_talwar]

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