Hello JetReports Community!
I am relatively new to JetReports. The problem I have probably isn't a tricky one but I still couldn't figure it out. I hope you guys will help!
I am trying to reverse the sign of the numbers I get from the database. The original formula is this one: =NF($Y33,"Net Change","Global Dimension 1 Filter","@@"&$P33,"Global Dimension 2 Filter","@@"&$R33,"Global Dimension 3 Filter","@@"&$T33,"Global Dimension 4 Filter","@@"&$V33,"Date Filter",$H$3)
Whenever I am adding "-" in front of the NF (=-NF), in some cases I get Excel error (#VALUE). I think whenever amount is not Zero, it works, but for Zeros it doesn't. If I remove "-", no errors whatsoever.
I also tried to do NF Formula * (-1) to get opposite sign, but that didn't work either. Can you help?
Thank you!
Date
Votes
7 comments
-
Jet Reports Historic Posts Official comment Well this formula shouldn't be too long since it is less than 1024 characters (assuming you are using Excel 2007 or later which I think you are since you uploaded an xlsx file). What happens if you try my original suggestion and put the NF in its own cell in a hidden column (without the -) and the just reference it in your IF statement? I will do this on your report for the first NF in Row 33 which appears to be a problem (cell AA in this case). What happens in this case?
Regards,
Hughes -
Jet Reports Historic Posts You could use an Excel IF statement to test whether the value is 0 or blank. If your original NF function is in cell F5, then you could do something like this:
=IF(OR(F5=0,F5=""),F5,F5*-1)
Does something like that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for the reply,
I tried IF but it comes as #VALUE if linked to the sell in question.
And what is the difference between Zero and blank, does Jet Report treat them differently?
If I change NF to positive the after running the report I get blanks (I think). I can't drilldown either. -
Jet Reports Historic Posts I know what you mean now by "Zeros vs Blanks". Blanks are when there is now transaction recorded for the combination of Dimensions. It appears that #VALUE comes only for blanks. But then my question is, why NF positive works but negative doesn't?
-
Jet Reports Historic Posts I don't think this is a Jet problem; it's just Excel. Excel won't let you multiple a blank by -1; that doesn't make any sense. You can only multiply numbers in Excel, not blanks. Excel doesn't care about a + because the + doesn't do anything (there's no actual operation performed in that case).
I'm still pretty sure you can solve this problem with an Excel IF function. Can you attach the an example report with an NF that has returned a blank (just run it against a date range or something that doesn't have any data so the NF returns blank)?
Regards,
Hughes -
Jet Reports Historic Posts Sure, here is a sample file that returns blanks/#VALUE : https://www.dropbox.com/s/b07f2wscvvm1vfo/10130_523003_error.xlsx
Originally I don't multiply it by -1, I simply add "-" before NF.
Looks like I made it via IF :) but my formula is just too long: =IF(OR(NF($Y33,"Net Change","Global Dimension 1 Filter","@@"&$P33,"Global Dimension 2 Filter","@@"&$R33,"Global Dimension 3 Filter","@@"&$T33,"Global Dimension 4 Filter","@@"&$V33,"Date Filter",$H$3)=0,NF($Y33,"Net Change","Global Dimension 1 Filter","@@"&$P33,"Global Dimension 2 Filter","@@"&$R33,"Global Dimension 3 Filter","@@"&$T33,"Global Dimension 4 Filter","@@"&$V33,"Date Filter",$H$3)=""),NF($Y33,"Net Change","Global Dimension 1 Filter","@@"&$P33,"Global Dimension 2 Filter","@@"&$R33,"Global Dimension 3 Filter","@@"&$T33,"Global Dimension 4 Filter","@@"&$V33,"Date Filter",$H$3),NF($Y33,"Net Change","Global Dimension 1 Filter","@@"&$P33,"Global Dimension 2 Filter","@@"&$R33,"Global Dimension 3 Filter","@@"&$T33,"Global Dimension 4 Filter","@@"&$V33,"Date Filter",$H$3)*-1)
I guess I am doing something wrong, can you help?
Thank you. -
Jet Reports Historic Posts Hi Hughes,
I see what you mean, yes, that worked as well! Thanks a lot for your help!
Please sign in to leave a comment.