0

Change to negative value for Document Type "Credit Memo" in a NL("Sum")

Hannes Holst

September 19, 2018 15:14

None

Follow

Hello,

I want to show a monthly figure of all Sales Orders in a single cell.
Orders and Invoices should have a positive Amount. And Credit Memos and Return Orders should have a negative Amount.

NL("Sum","Sales Header","Amount","Posting Date","01/09/18..30/09/18")

Please ignore the fact about using the Amount-field and different currencies for now.

How do I have to modify the function to sum Orders and Invoices with a positive value and Credit Memos and Return Orders with a negative amount?

 E.g.

Document Type No.   Amount
Order         SO001  95.00
Order         SO005  11.00
Invoice       SI011  55.50
Credit Memo   CM004 -10.00
Return Order  RO001 -22.00
              Total 129.50

Kind regards,
Hannes

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Harry Lewis September 24, 2018 17:06

  Hello Hannes -

  You can change the sign of your function by placing a negative sign in front of your NL(Sum) function:

  

• 0
  
  Hannes Holst September 25, 2018 10:29

  Hi Harry,

  Thanks for your answer.
  Your suggestion will not work in my situation, because I have different Document Types in my result.
  Orders and Invoices should have a positive amount. Credit Memos and Return Orders should have a negative amount.

  Your suggestion would imply that Orders and Invoices would have a negative amount.

  Kind regards,
  Hannes

• 0
  
  Harry Lewis September 25, 2018 14:17

  Hello Hannes -

  You could use multiple NL(Sum) functions - each filtered by a separate Document Type.  You could then add or subtract them as needed.

  As an example:

  =NL("Sum","Sales Header","Amount","Posting Date",$C$3,"Document Type","Order")-NL("Sum","Sales Header","Amount","Posting Date",$C$,"Document Type","Credit Memo")

  Or, place them in separate columns and sum them using Excel:

  

   

• 0
  
  Hannes Holst September 25, 2018 14:39

  Hi,

  Yes, I tried it this way.
  But going this way, when opening the Lookup, JetReports will show only the data of the last NL-function.

  That's potentially confusing for an end user and raises questions, because it is not possible to see all data which results in the total figure.

   

  Thanks,
  Hannes

  Edited by Hannes Holst September 25, 2018 14:40
• 0
  
  Shu Foster June 20, 2022 08:52

  Hello Hannes

  I have the same problem, have you got a solution for this please?

  Thanks

  Shu