Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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??
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.
try
FirstSortedValue({1} ORGANIZATION_NAME, -aggr(sum({1} AR_INVOICE_AMOUNT),ORGANIZATION_NAME))
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 ?
Kindly go through the attachment
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)
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 ?
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.
Great!! Worked fine!! God bless.