Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
renjithpl
Specialist
Specialist

Show top one org name in a text box

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

try

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


talk is cheap, supply exceeds demand
renjithpl
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Kindly go through the attachment

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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)


talk is cheap, supply exceeds demand
renjithpl
Specialist
Specialist
Author

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
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
renjithpl
Specialist
Specialist
Author

Great!! Worked fine!! God bless.