0

Converting to a negative

Jet Reports Historic Posts

June 17, 2015 09:10

None

Follow

I'm writing a sales report showing document type 3 and 4 which is Invoices and Returns. However, the returns come out as a positive number and I need to convert this to negative. I've tried the following formula but it doesn't work:

Formula((if([@[Document Type]]=4,[@[Unit Extended Price]]*-1,[@[Unit Extended Price]]))

What am I doing wrong?

Thanks
Alan

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts June 17, 2015 17:30

  Hi Alan,
  
  I just tried it and got it to work. The only difference (I think) is that I put an = sign in front of the IF() statement. Below is my NP(Formula).
  
  NP("Formula","=if([@[SOPTYPE (SOP Type)]]=4,[@[XTNDPRCE (Extended Price)]]*-1,[@[XTNDPRCE (Extended Price)]])")

• 0
  
  Jet Reports Historic Posts June 17, 2015 17:38

  Hi Alan.
  
  Try putting a negative sign in the beginning or multiple by negative 1 and it should work for Jet functions ex: =-NL("Sum","SOP30200","DOCAMNT","DataSource=","GP_Dynamics","SOPTYPE","4")
  
  It looks like you want the amount to show as a positive no matter what. You could probably do this pretty easy using the absolute value. It looks like you are using a table to do this so I'll show the example using the table function. Try this as your formula: =NP("Formula","=abs([@[ACCTAMNT (Account Amount)]])")
  
  Jason

• 0
  
  Jet Reports Historic Posts June 18, 2015 07:51

  The Kloser/Jason T
  
  Thanks for your input guys, appreciate it……
  
  The Kloser, your formula and mine are very similar, although I had missed a = sign before "if". I put this in and it still isn't working. I don't think I mentioned this previously, but I'm not getting an error with the formula, it is showing my formula as text in the cell rather than returning a value.
  
  Jason T, your suggestion I reckon would turn every value to a negative. I only want to show Returns as negative with Invoices being positive.
  
  I've attached a copy of my report for you to look at
  
  Thanks
  Alan

  • Sales MAster Using Sales Line Items.xlsx
• 0
  
  Jet Reports Historic Posts June 18, 2015 13:52

  Thanks for sending the report. I opened it up and you are using a custom View so I can't run the report to test. But when I opened up the report, I noticed that your NP(formula) didn't have () for the IF statement.Here is what you have currently:
  
  NP("Formula","=if[@[SOP Type]]=Return,[@[Extended Price]]*-1,[@[Extended Price]]")
  
  I think it should work if you change yours to:
  
  NP("Formula","=if([@[SOP Type]]=Return,[@[Extended Price]]*-1,[@[Extended Price]])")
  
  I wish I had your View so I could test this before sending you a response but I compared this to mine (which worked on my machine), and it looks right.

• 0
  
  Jet Reports Historic Posts June 18, 2015 15:49

  The Kloser
  
  Thanks for your help.
  
  Ok, I made this change and it returned a #NAME?. This tells me it doesn't like something, most likely the word "Return". So I changed this to the number that refers to Return which is 4 and it then gave me a value. However, it doesn't convert the returns to negative values. Which is what the formula tells it to do.
  
  Any ideas????
  
  Cheers
  Alan

• 0
  
  Jet Reports Historic Posts June 18, 2015 16:06

  The Kloser
  
  On further testing, I was making the change in the report itself. I went back to my template and changed the formula here. I did have to put " around the word return but it seems to have worked.
  
  I will test further tomorrow and if I have any further questions on this subject I will let you know
  
  Appreciate all your help
  
  Thanks
  Alan