Hi,
I'm hoping someone can help me out with this formula before I throw my laptop out of the window :)
I have written a jet report which shows invoiced sales (sales year to date) by customer in 1 column, then booked sales (sales for the rest of the year that haven't been invoiced yet) in another column, then a total in a third column.
I had to mess about a bit with the VAT in the booked sales column, as I was using Outstanding Amount field which includes VAT, and I wanted it excluding VAT.
At the moment, every time the report is run, I need to manually sort it by the total. I would like to see if it is possible to build the sort bit into the customer list, so it would show list of customers, sorted by (sum of invoiced sales + sum of booked sales).
Here is my attempt:
=NL("Rows","Customer","No.","Customer Posting Group","FOB","-=(NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"","""&$F$4&""")+(NL(""Sum"",""Sales Line"",""=IF(NF(,""VAT %"")>0,(NF(,""Outstanding Amount (LCY)"")/1.2),NF(,""Outstanding Amount (LCY)""))"",""Source No."",NF(,""No.""),""Posting Date"","""&$F$1&""")))","*","Link=","Value Entry","Source No.","=No.","Link=","Sales Line","Sell-to Customer No.","=No.")
I am getting a #VALUE! error and it says it can't evaluate the formula, and I think if I keep messing about with it myself I'm just going to make it worse!
Any help would be appreciated.
Date
Votes
5 comments
-
Jet Reports Historic Posts Official comment Aaah I figured it out finally. The problem isn't the syntax of the calc field. The problem is actually that it's just too long. Calc fields can only be a max of 256 characters long or Excel won't evaluate them. So I was able to fix the problem by putting some table/field names in cell references. So my formula that works ended up looking like this:
=NL("Rows","Customer","No.","Customer Posting Group","*","limit=","20","-=NL(""Sum"",$C$2,$C$3,""Source No."",NF(,""No.""),""Posting Date"","""&"*"&""")+NL(""Sum"",""Sales Line"",""=IF(NF(,""""VAT %"""")>0,(NF(,""""Outstanding Amount (LCY)"""")/1.2),NF(,""""Outstanding Amount (LCY)""""))"",""Sell-to Customer No."",NF(,""No.""),""Shipment Date"",""02/01/13..01/31/14"")","<>0")
Note that C2 holds Value Entry and C3 holds Sales Amount (Actual). Also, it's important that in this case I did not use the """&C2&""" sort of syntax because that would defeat the point. Also, it's an absolute cell reference and the value isn't changing so it's not necessary. Anyway, does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts My second attempt looks like this:
=NL("Rows","Customer","No.","Customer Posting Group","FOB","limit=","20","-=Local(NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"","""&$C$2&""")+NL(""Sum"",""Sales Line"",""=IF(NF(,""VAT %"")>0,(NF(,""Outstanding Amount (LCY)"")/1.2),NF(,""Outstanding Amount (LCY)""))"",""Sell-to Customer No."",NF(,""No.""),""Shipment Date"",""01/02/13..31/01/14""))","<>0")
I think the problem is with the IF formula I'm using to get the Outstanding Amount (LCY) excluding VAT, as if I change that formula to just a normal field ie "Amount", as below, it works fine.
=NL("Rows","Customer","No.","Customer Posting Group","FOB","limit=","20","-=Local(NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"","""&$C$2&""")+NL(""Sum"",""Sales Line"",""Amount"",""Sell-to Customer No."",NF(,""No.""),""Shipment Date"",""01/02/13..31/01/14""))","<>0")
I wonder if there's a better way for me to calculate the Outstanding Amount (LCY) figure excl VAT?
Maybe I could do Amount * Outstanding Quantity, and then use the Currency Factor in the Sales Header to convert it to LCY?
If anyone has any advice I would love to hear it. -
Jet Reports Historic Posts You are right that the IF function is the problem. Since that is a a calculated field INSIDE a calculated filter field, you actually would have to quadruple quote everything like this:
=NL("Rows","Customer","No.","Customer Posting Group","FOB","limit=","20","-=Local(NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"","""&$C$2&""")+NL(""Sum"",""Sales Line"",""=IF(NF(,""""VAT %"""")>0,(NF(,""""Outstanding Amount (LCY)"""")/1.2),NF(,""""Outstanding Amount (LCY)""""))"",""Sell-to Customer No."",NF(,""No.""),""Shipment Date"",""01/02/13..31/01/14""))","<>0")
I'm imaging this should be really really slow, but hopefully it will work. Is that the case?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for the formula but unfortunately it is still showing VALUE error and saying it can't evaluate the formula :(
It is saying it can't evaluate this bit specifically:
=Local(NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"","""&$C$2&""")+NL(""Sum"",""Sales Line"",""=IF(NF(,""""VAT %"""")>0,(NF(,""""Outstanding Amount (LCY)"""")/1.2),NF(,""""Outstanding Amount (LCY)""""))"",""Sell-to Customer No."",NF(,""No.""),""Shipment Date"",""01/02/13..31/01/14"")
Is there a better way for me to work out the Outstanding Amount in both local currency and excluding VAT, than using the IF formula I've used?
Thanks again for your help so far :) -
Jet Reports Historic Posts Thanks Hughes! You are the best!
Had to do a bit of fiddling to get it to pick up all of the customers and filter correct posting dates etc but now it is working fine :)
Thanks as well for the tip about putting table/field names in cells and referencing them to save space :)
Thank you so much!
Please sign in to leave a comment.