0

NP FORMULA not returning a value

Jet Reports Historic Posts

February 01, 2013 07:55

None

Follow

Hi,

I am using latest Jet essentials and the table builder in this case with NAV 2009 r2 running on SQL svr 2008 R2.
I added a calculated field being =NP("Formula","[@[Sales Invoice Line - Line Amount]]-[[@[Sales Invoice Line - Quantity]]*[@[Sales Invoice Line - Unit Cost]]]") but the result is
'=[@[Sales Invoice Line - Line Amount]]-[[@[Sales Invoice Line - Quantity]]*[@[Sales Invoice Line - Unit Cost]]] and not the result of the intended calculation.

I also then want to create a Pivot table on this generated information on another sheet to create pivot charts, graphs etc but have tried and can't seem to set the range.

I am new to Jet so please bear with me.

I have attached the sample I am struggling with

Many thanks

Thomas

• Sales profit attempt 5.xlsx

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts February 01, 2013 08:11

  Hi Thomas,
  
  If you add a "=" in your formula it should work. It's just like a formula in Excel.
  
  So, the formula becomes =NP("Formula","=[@[Sales Invoice Line - Line etc.
  
  Does this work for you?

• 0
  
  Jet Reports Historic Posts February 01, 2013 08:46

  Hi Hans,
  
  I tried your suggestion and am getting the exact same result.
  Do you have anything else for me to try?
  I also tried a straight forward simple excel calc referencing cells but that did not work either.
  Am using language English - US, list separator is a comma ","
  
  Thanks

• 0
  
  Jet Reports Historic Posts February 01, 2013 09:43

  Hi Thomas,
  
  When I read the topic I noticed the missing "=" and responded. But now that I try the report too I found that there's another issue as well. It's all about brackets: the [ ] brackets were applied instead of ( ).
  
  This formula works for me:
  =NP("Formula";"[@[Sales Invoice Line - Line Amount]]-([@[Sales Invoice Line - Quantity]]*[@[Sales Invoice Line - Unit Cost]])")
  
  Can you do the other formula yourself?

• 0
  
  Jet Reports Historic Posts February 01, 2013 10:21

  Hi Hans,
  
  That worked.
  This is the corrected formula on the other one
  =NP("Formula";"(([@[Sales Invoice Line - Line Amount]]-([@[Sales Invoice Line - Quantity]]*[@[Sales Invoice Line - Unit Cost]]))/[@[Sales Invoice Line - Line Amount]])*100")
  which seems to be working.
  Had no clue about using the round brackets as I was under the impression with the NP formula that square brackets was the way to go.
  
  Thanks for solving that one