0

NF-NF Filters

Jet Reports Historic Posts

March 10, 2010 17:40

None

Follow

Hello everyone,

This is my first time posting on the site even though i have been using the site as a resouce for a couple years now. Here's my question. I have a NL statement that reads…

=NL("Rows=2","Item",,"No.",$C$5,"=NF(,""Inventory"")-NF(,""Qty. on Sales Order"")","<0","Inventory",">0")

The statement works properly but what im trying to do is add a date filter on the NF(""Qty. on Sales Order"") and I cant seem to make it work. If anyone out there has a suggestion on how I can write that in it would be much appreciated.

Thanks

Chris

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts March 12, 2010 15:43 Official comment

  Hi Chris,
  
  I went ahead and tested the following and it seems to work:
  
  =NL("Rows=2","Item",,"No.",$C$5,"=NF(,""Inventory"")-NF(,""Qty. on Sales Order"",""Date Filter"",""1/1/2010..3/31/2010"")","<0","Inventory",">0")
  
  Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 12, 2010 16:47

  One thing I would watch out for is if you use a cell reference.
  
  This will NOT work:
  
  =NL("Rows=2","Item",,"No.",$C$5,"=NF(,""Inventory"")-NF(,""Qty. on Sales Order"",""Date Filter"",D6)","<0","Inventory",">0")
  
  You have to use 3 quotes and the ampersand & when using a cell reference (as follows):
  
  =NL("Rows=2","Item",,"No.",$C$5,"=NF(,""Inventory"")-NF(,""Qty. on Sales Order"",""Date Filter"","""&D6&""")","<0","Inventory",">0")

• 0
  
  Jet Reports Historic Posts March 12, 2010 22:52

  Hi emis!
  
  That is a good point! You actually can use a cell reference if it's an absolute cell reference (for example if the value is coming from your options sheet or if it's a value at the top of the report sheet whose position on the sheet does not change. However, since the calculated filter field is just a string to Excel, the cell reference will not be updated when rows or columns are replicated, so if you want to reference data that is changing in your row or column, then you need to do as you suggested and put the cell reference outside the string so Excel will update it when we replicate rows and columns.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 17, 2010 01:20

  Hughes,
  
  This worked perfectly….thank you for the help!
  
  =NL("Rows=2","Item",,"No.",$C$5,"=NF(,""Inventory"")-NF(,""Qty. on Sales Order"",""Date Filter"",""1/1/2010..3/31/2010"")","<0","Inventory",">0")