0

Sales Prices within Starting and Ending Date

Jet Reports Historic Posts

January 29, 2012 17:10

None

Follow

Hi Experts,

I would like to list Sales Prices, where the date in cell E14 is in range of Starting and Ending Date. Please note that the Starting or Ending Date could be empty.

E13=20000
E14=10.02.2012

=NL("Rows";"Sales Price";;"Sales Code";$F$13;$E$14;"=NP(""Datefilter"";NF(;""Starting Date"");NF(;""Ending Date""))")

This functions of course does not work. E14 isn´t a field in the Sales Price Table. But how do I solve the problem?

Thanks

Frank

• Facebook
• Twitter
• LinkedIn

6 comments

• 
  Jet Reports Historic Posts January 31, 2012 16:13 Official comment

  Frank,
  
  Okay then I think all you need to do is modify my formula to include blank starting and ending dates in the filter. You can filter for blanks by using 2 single quotes ('). So you want the starting and ending dates to be either blank or the date filter which might look something like this:
  
  =NL("Rows";"Sales Price";;"Sales Code";$F$13;"Starting Date","''|"&NP("DateFilter";;$E$14);"Ending Date";"''|"&NP("DateFilter";$E$14))
  Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 30, 2012 18:16

  Hi Frank,
  
  I think maybe you're going about this a little backwards. I think you might want to do something like this:
  
  =NL("Rows";"Sales Price";;"Sales Code";$F$13;"Starting Date",NP("DateFilter";;$E$14);"Ending Date";NP("DateFilter";$E$14))
  This would give you the sales prices where the starting date is less than the value in E14 and the ending date is greater than the value in E14, which I think is what you are looking for. Does this work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 30, 2012 21:02

  Hi Hughes,
  thanks for your answer. But your functions doesn´t work, if the starting or ending Date is blank.
  
  Regards
  Frank

• 0
  
  Jet Reports Historic Posts January 30, 2012 22:21

  Frank,
  
  The question is, do you want the blanks included or not included?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 31, 2012 15:48

  Here is an example to show what I mean:
  
  Datefilter: 20.01.2011
  
  Sales Prices: Starting Date: 01.01.2011, Ending Date: blank
  This entry should be listet
  
  Sales Prices: Starting Date: 01.01.2011, Ending Date: 31.01.2011
  Should be listed
  
  Sales Prices: Starting Date: 01.01.2011, Ending Date: 15.01.2011
  Should not be listed
  
  The same logic should be used with blank Starting Date

• 0
  
  Jet Reports Historic Posts January 31, 2012 17:47

  Perfect! Thanks!