Firstsortedvalue returns Null

[#1]

Hi everybody, I have the following expression:

FirstSortedValue(DescriptionFixed,Aggr(sum(Amount-OriginalAmt), DescriptionFixed))

Since I have exactly the same amount for two different items the firstsortedvalue brings me NULL.

How can I get just one item instead of NULL?  Either item will work.

Any help will be greatly appreciate it

[mrossoit]

Hi,

I can't try it at the moment.

Try to add distinct keyword:

FirstSortedValue(DISTINCT DescriptionFixed,Aggr(sum(Amount-OriginalAmt), DescriptionFixed))

Regards

MR

[Gysbert_Wassenaar]

You're already showing the value you want to calculate in the dimension column. Why do you think you need that expression?

[#4 · Accepted solution]

Hi,

Please try:

=FirstSortedValue(country,-Aggr(sum(Amount-OriginalAmt)+rank(DescriptionFixed,DescriptionFixed)/10,DescriptionFixed))

This will do the second sorting by alphabetical order (DescriptionFixed field).

Hope this helps.

Kind regards,

Nuno

[#5]

Hi Nuno, thank you very much it ran smoothly, I really appreciate your help.

What is the /10 stand for?

Best,

Eric !

[#6]

I already did Massimiliano, but did not work. Thanks for your help though

Nuno's approach worked perfectly

[#7]

Hi Eric,

Thanks. I can't take all the credit for the formula, because it's adapted from cleveranjos‌.

The /10 is only to get it in a small factor that the second sort doesn't mess up the first.

Kind regards,

Nuno

[#8]

Hi Nuno. I have another question for you. I just created a backorder app:

Our ideal billing process is for every Cust PO  one invoice is created, however that is not happening and my App is showing unreal BO.

Example:

CustPO: 0029075 is related with 2 invoices: SI015361 and SI015362. This causes that the report show unreal BO, for example the first LineNo_"200000" on the report has $1,706 on PO Amount, but 0 invoiced, meaning that the line is consider as a BO, however the second row same item and same LineNo_"200000" show $1,706 on PO Amount and $1,706 invoiced. Hence the first line it is not a BO.

I think that writing an IF statement on the script can be a good approach to not show duplicated keys, or even better firstsortedvalue, since I need to show the value that has Invoiced amount and prevent the unreal BO, but I do not know how to do it. Any thoughts?

KDup

0029075|200000

0029075|200000

0029075|290000

Thank you very much for your help..

Best,

Eric

[#9]

Hi Eric,

Looking at you data I would just do something like:

Load *

where Invoiced (1st column) = 'Invoiced'

or

Load *

where Invoiced (11th column) <> 0

One of those should remove the duplications. depending on the rule you prefer.

Firstortedvalue or other more complicated solutions are only necessary if your request is more complex than I understood.

Kind regards,

Nuno

[#10]

Hi Nuno, thanks you very very much for the tip, however I still need to Show the Invoiced=(backorder) on the first approach and on the second there can be situations where there is 0 on the eleven column meaning that there was zero inventory hence a BO.

I was trying to use de Dup key that I created with  WHERE but the result it is not correct, this is part of the script:

// Help to create a concatenate field from diferent tables and filter duplicated Backorders

BODdupTemp:

Load Distinct

Invoice As Invoice,

"#CustPO" As KCustPO

Resident SalesInvoice;

Join

Load Distinct

Invoice As Invoice,

"Line No_" As KLineNo

Resident SalesInvoiceline;

SalesInvoiceline:

Load Distinct

Invoice&'|'&KLineNo AS Key,

KCustPO&'|'&KLineNo As KDup

Resident BODdupTemp;

where KDup   <> KDup;