Show top one org name in a text box

[renjithpl]

Hi All,

I want to show the top number one org name based on the sales

i used below formula

only(if(aggr(rank(Sum ({1}AR_INVOICE_AMOUNT)),ORGANIZATION_NAME)=1,ORGANIZATION_NAME))

but when i click clear button it does not show the organization name rather it gives null value

what went wrong??

[Gysbert_Wassenaar]

try

FirstSortedValue({1} ORGANIZATION_NAME, -aggr(sum({1} AR_INVOICE_AMOUNT),ORGANIZATION_NAME))

[renjithpl]

thanks for the reply, it dint work, but the expression i wrote gave me the answer,

now how can i get the value of the top org name ?

[renjithpl]

Kindly go through the attachment

[Gysbert_Wassenaar]

You have organisations without a name. That's why the functions return -

Try this in a text box:

=FirstSortedValue({1}ORGANIZATION_ID, -aggr(sum({1} AR_INVOICE_AMOUNT),ORGANIZATION_ID))

You'll get ID 495. The sum of AR_INVOICE_AMOUNT is 320000. That's a lot higher than the total for NAFFCO NJF Dubai which is 48668.43

Try this to correct for the missing names:

Highest:

=FirstSortedValue({1<ORGANIZATION_NAME={'*'}>}ORGANIZATION_NAME, -aggr(sum({1<ORGANIZATION_NAME={'*'}>} AR_INVOICE_AMOUNT),ORGANIZATION_NAME))

Second highest:

=FirstSortedValue({1<ORGANIZATION_NAME={'*'}>}ORGANIZATION_NAME, -aggr(sum({1<ORGANIZATION_NAME={'*'}>} AR_INVOICE_AMOUNT),ORGANIZATION_NAME),2)

[renjithpl]

Hello Gysbert sorry for the late reply, i tried, it works fine, yes there are some organization without names, as i am testing this application, Once i get the full data this will work exactly i want,

i have one more question, how can i get the corresponding value for those names in another text box ?

[Gysbert_Wassenaar · Accepted solution]

Like this:

highest

=FirstSortedValue({1<ORGANIZATION_NAME={'*'}>}aggr(sum({1<ORGANIZATION_NAME={'*'}>} AR_INVOICE_AMOUNT),ORGANIZATION_NAME), -aggr(sum({1<ORGANIZATION_NAME={'*'}>} AR_INVOICE_AMOUNT),ORGANIZATION_NAME))

second highest

=FirstSortedValue({1<ORGANIZATION_NAME={'*'}>}aggr(sum({1<ORGANIZATION_NAME={'*'}>} AR_INVOICE_AMOUNT),ORGANIZATION_NAME), -aggr(sum({1<ORGANIZATION_NAME={'*'}>} AR_INVOICE_AMOUNT),ORGANIZATION_NAME),2)

Once you have no organisations without a name you can get rid of all the {1<ORGANIZATION_NAME={'*'}>} bits.

[renjithpl]

Great!! Worked fine!! God bless.