0

How do I get the difference of 2 values?

Jet Reports Historic Posts

May 14, 2012 10:18

None

Follow

Hi everyone,

Long time lerker first time poster here, I hope to get an answer for this very annoying problem I am facing.

anyway, I have to get the difference of two values and it is not working as well as I had hoped.

this is the formula

=NL("Sum";"50011 Gebuchte Verkaufszeile";"15 Menge";"6 Nr.";$B38;"2 Verk. an Deb.-Nr.";$J$35;"10 Warenausg.-Datum";M$20;"50099 Belegart";"Rechnung")- NL("Sum";"50011 Gebuchte Verkaufszeile";"15 Menge";"6 Nr.";$B38;"2 Verk. an Deb.-Nr.";$J$35;"10 Warenausg.-Datum";M$20;"50099 Belegart";"Gutschrift")
Our DB is in German, so please excuse the German terminology.

I tried putting a bracket right after the = sign (to start right before NL and end right after the the last ) after Gutschrift).

I always end up getting wrong number, I tried pasting each NL alone in Excel, I got correct indivual results, but the subtraction is the problem.

Any ideas?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts May 14, 2012 14:10

  Hmmm, I'm not sure I understand exactly. Are you saying that when you put the 2 NL(Sum) functions in different cells and then do a subtraction on those 2 cells you get the number you are looking for, but when you put the whole thing in 1 cell you don't? Or is it more complicated than that?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts May 17, 2012 14:48

  Newbie response here, but I had a similar issue that I solved.
  
  I also wanted to see both columns; in your example, "Rechnung" and "Gutschrift" I believe.
  First I generated the data as a table (showing "Rechnung" and "Gutschrift").
  Then just outside the table, I clicked the cells to make the calculation. The cell will show the result.
  Click in the cell and it says something like =50011_Gebuchte_Verkaufszeile[@[Rechnung]]-50011_Gebuchte_Verkaufszeile[@[Gutschrift].
  Copy that formula, then reenter Design mode, and add another column for the "copied" formula [ =NP("Formula","=50011_Gebuchte_Verkaufszeile[@[Rechnung]]-50011_Gebuchte_Verkaufszeile[@[Gutschrift]") ]. Don't forget to expand the Table size by one column to display the new column. You may need to edit the actual spelling of the formula, but this did the trick for me.
  
  That did it for me, and also preserved the original two data points so one can also check the "Rechnung" and "Gutschrift" values if the new calculated field is not making sense.