Hi,
I am using the "Outstanding Amount (LCY)" field in the Sales Line, which includes VAT.
I would like to work out this amount excluding VAT, using the "VAT %" field, also in the Sales Line.
I am using the following formula but getting a VALUE error:
=NL("Sum","Sales Line","=IF(NF(,""VAT %"")=0,NF(,""Outstanding Amount (LCY)""),NF(,""Outstanding Amount (LCY)""),/NF((,""VAT %"")/100)+1)","Sell-to Customer No.",$F31,"Shipment Date","01/02/13..31/01/14")
I would be really grateful if someone could tell me what I'm doing wrong or suggest a better way of working this out!
Thank you
Date
Votes
9 comments
-
Jet Reports Historic Posts Official comment Hi,
It looks like there are several mistakes in the formula. You have an extra comma bettwen the NF and the /. Also, you have an extra ( after your last NF. You're obviously trying to put parenthesis around something there, but I'm not sure where the first one should go. Maybe it's something like this:=NL("Sum","Sales Line","=IF(NF(,""VAT %"")=0,NF(,""Outstanding Amount (LCY)""),NF(,""Outstanding Amount (LCY)"")/(NF(,""VAT %"")/100)+1)","Sell-to Customer No.",$F31,"Shipment Date","01/02/13..31/01/14")
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts That worked perfectly! Thank you so much!!!
-
Jet Reports Historic Posts Hi, how do I enter this formula? I am trying to do the same, just without the sell to customer no & shipment date.
Is this a nested jet function, if so, what do I enter where, I did try but it crashed my jet, so must have done something wrong.
All I want is an outstanding Amount (LCY) by Line, ex vat, which this looks like it is?
please advise
Nav 2009 R2, Jet Essentails -
Jet Reports Historic Posts hi,
please post your code, that maeks it easy to find the Problem ;)
If you do this code with all of your sales lines (without any filter) , this maybe be to much….
So, please post your code..
regards
jetsetter -
Jet Reports Historic Posts hi jetsetter,
that is my problem, I do not have any code to post, as I cant get it to work, I tried to use the code in this post, but how & where do I put this code??? I copied & pasted into Excel into the report in Designer mode & this crashed Excel, so something was wrong, but what?
Is it in Function wizard, if so, what goes on what lines?
What else can I supply you?
All I want to know from a sales line, is what is outstanding to ship, in (LCY) ex vat
Thanks
Ian -
Jet Reports Historic Posts Hi Jetsetter, just seeing if you are able to help or ClaireBoardmans ?
Do I add via function wizard a new NL, if so, what of the below goes in each line?
Do I need the Sum in the first What as I am looking to report on every Sales Line
The Table will be Sales Line
Does the rest of the code go into the Field area, how exactly is it entered?
Where does the IF statement get entered?
Are there any filters? I don't need sell to customer no or a Shipment date filter
Thanks -
Jet Reports Historic Posts To add, my options on the Sales Line are:-
Type = Item|resource
Outstanding Quantity <>0
Document Type = Quote|Order
The end result would be every Sales Line that has an outstanding Qty for all Quotes & Orders so that we can see potential Values of what we are quoteing on & what we have orders for
If I use the code as below, this seems to total up for all Lines, so does not work, as I need the Outstanding Amount (LCY) ex vat to be on every Sales Line
Thanks -
Jet Reports Historic Posts hi,
if you want to see every sales line with several fields, you could use a "quick and dirty"-solution..:
just give out the fields "Outstanding Amount (LCY)" (eg in cell $L9) and "VAT %" ( eg in cell $M9)for each line
then make an excel-function in another cell ( N$9) where you can calculate you "net outstanding amount) like this:
=$L9/(($M9/100)+1)
does this help?
regards
jetsetter -
Jet Reports Historic Posts Hi Jetsetter,
I have managed to solve this with Help from Jet Reports, I was very close, but did not know it was an NP Function that would make the Formula work.
For reference, here is my final code:-
=NP("Formula","=IF([@[VAT %]]=""0"",[@[Outstanding Amount (LCY)]],([@[Outstanding Amount (LCY)]]/([@[VAT %]]/100+1)))")
Thanks for your advice,
Regards,
Ian
Please sign in to leave a comment.