Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
I can't try it at the moment.
Try to add distinct keyword:
FirstSortedValue(DISTINCT DescriptionFixed,Aggr(sum(Amount-OriginalAmt), DescriptionFixed))
Regards
MR
You're already showing the value you want to calculate in the dimension column. Why do you think you need that expression?
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
Hi Nuno, thank you very much it ran smoothly, I really appreciate your help.
What is the /10 stand for?
Best,
Eric !
I already did Massimiliano, but did not work. Thanks for your help though
Nuno's approach worked perfectly
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
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
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
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;