Goodmorning,
I am trying to sort an overview of Customer on the sum of the fields "Inv. Amounts (LCY)" and "Cr. Memo Amounts (LCY)" and limit this to an output of 20 records.
This is the formula to do this on the "Inv. Amounts (LCY)" field only:
=NL("Rows";"Customer";"No.";"Datasource=";$E$3;"0";$E$4;"Date Filter";$F$13;"-Inv. Amounts ($)";"<>0";"Limit=";20)
Unfortunately I can't get it to work using both fields.
I've read a lot of posts on the forum, but didn't find the answer.
Any ideas greatly appreciated. Thanks in advance!!!
Best regards,
Gerard
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi Gerard,
Sorry, I didn't understand your question very good.
I think your result should be coming from this formula:
=NL("Rows";"Customer";"No.";"Datasource=";$E$3;"0";$E$4;"Date Filter";$F$13;"Inv. Amounts ($)";"<>0";"-=NF(;""Inv. Amounts ($)"")-NF(;""Cr. Memo Amounts ($)"")";"*";"Limit=";20)
In this formula you're sorting on the total of Inv. Amounts - Cr. Memo Amounts.
Good luck,
Bert -
Jet Reports Historic Posts Hi Gerard!
It works perfectly fine for me when I try your formula without the filterfields "Date Filter" and "0" so I assume the problem must be with those filterfields… -
Jet Reports Historic Posts Hi BertYNP,
Thanks for your response. I am not sure what you are trying to say.
The formula I pasted in the post works just fine. However I do not know how to add the second field in the calculation of the sort-field (invoice amounts -/- credit memo amounts).
Best regards,
Gerard -
Jet Reports Historic Posts Hi Bert,
Sorry it took a while to respond!
Your formula was a good start. I made a few small adjustments and ended up with this:
=NL("Rows";"Customer";;"=(NF(;""Inv. Amounts (LCY)"")-NF(;""Cr. Memo Amounts (LCY)""))";"<>0";"Datasource=";$E$3;"0";$E$4;"Date Filter";$F$13)
Thanks for pointing me in the right direction!
Best regards,
Gerard
Please sign in to leave a comment.