0

Sort by a division calculated field

Jet Reports Historic Posts

May 13, 2013 11:59

None

Follow

Hi all,

I can do this easily enough:

=NL("Rows","Customer",,"-Credit Limit","*","-=NF(,""Credit Limit"")-NF(,""Balance"")",">0.60","Limit=","10")
However, if I change the subtraction in the calculation to a division, i.e.

=NL("Rows","Customer",,"-Credit Limit","*","-=NF(,""Credit Limit"")/NF(,""Balance"")",">0.60","Limit=","10")
… the report comes up with an error "

Excel is unable to evaluate '-=NF(,""Credit Limit"")/NF(,""Balance"")'.

Just wondering if you guys had any ideas.

NAV 5.0 Sp1 Update 2

Jet Essentials 2013 Beta 14.0.13102.0 (64-bit)

Excel 2010 SP2 (Beta) 14.0.7011.1000 (64-bit)

Regards,
Stephen

• Facebook
• Twitter
• LinkedIn

6 comments

• 
  Jet Reports Historic Posts May 13, 2013 17:11 Official comment

  Hi Stephen,
  
  I can't quite reproduce your problem. I can run this formula without any problems:
  
  =NL("Rows","Customer",,"-=NF(,""Balance"")/NF(,""Credit Limit (LCY)"")","*","Credit Limit (LCY)","<>0")
  The results in this case sort correctly. I do have to add the filter for credit limit not equal to zero because otherwise I get a DIV/0 error. Could you have some zero values for credit limit in your table?
  
  You did mention in your original post that you are running a beta version of Excel 2010. This seems like it must be a typo since I can't imagine any beta versions of Excel 2010 is still running with Excel 2013 released. However, you are using a beta version of Excel, this could be a problem since these have been known to have bugs in the past. Is this the case?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts May 13, 2013 14:16

  Hmm, strange. As far as I remember I have seen that bug while working with Excel without JR.
  It was always a division with decimals causing a similar error. I had to enter a * in order to multiply the result first and then I was able to enter the / with the right result.
  
  But I guess that work-around will not work in that case…
  
  Cheers
  André

• 0
  
  Jet Reports Historic Posts May 13, 2013 14:28

  Hi André,
  
  Multiply works fine - just the divide… actually I am dividing by numbers in the hundreds of thousands and not by decimals…
  
  Very weird error I must say.
  
  
  Anyone else?
  
  
  Regards,
  Stephen

• 0
  
  Jet Reports Historic Posts May 13, 2013 14:57

  I think I have it sussed…
  
  D'Oh
  
  ANSI Escape Sequences always start with a '/'.
  
  Rewrote the formula…
  
  =NL("Rows","Customer",,"-=NF(,""Balance"")&""/""&NF(,""Credit Limit"")","*")
  But I think it still needs testing…

• 0
  
  Jet Reports Historic Posts May 13, 2013 16:37

  OK this is still not giving me the results I am looking for:
  
  What I am trying to do is sort by "Balance"/"Credit Limit", in descending order.
  
  But the result looks like a mess…
  
  
  https://www.dropbox.com/s/6s4ygv8gz6kai11/Jet%20Reports%20-%20sort%20by%20division%20calc%20field.png

• 0
  
  Jet Reports Historic Posts May 13, 2013 17:50

  Hi Hughes,
  
  Excel 2010 has released a beta for SP2. That's what I meant…. sorry for not being clear.
  
  Also, pasting your formula into the worksheet seems to have done the job.
  
  Very very strange.
  
  At least now I have it sorting the way I want. Yes I could have zero credit limits, so thanks for that (would have hit them sooner or later!)
  
  
  Thanks Hughes.