0

Range in Function Wizard

Jet Reports Historic Posts

January 03, 2013 14:28

None

Follow

Im trying to SUM records based on a date range using the NL function in the function wizard.

I have two fields, C5 = Lower Date Limit and D5 = Upper Date Limit, that form the range for my sample and I want to sum "Daily Call Count Target" from the DB based on entries where "Start Date" falls within this range. How do I create the filter to sum?

Currently I have this…

=NL("First","KPI Targets","Daily Call Count Target","sec1_owneridname",C12,"Start Date",D5,"Start Date",F5)

• Facebook
• Twitter
• LinkedIn

2 comments

• 
  Jet Reports Historic Posts January 03, 2013 15:29 Official comment

  Hi,
  
  You have a just a couple problems with your formula. First, you are filtering by the same field twice on the lower and upper date limits. What you actually need to do is filter only once, and create a date range filter that looks like this: "date1..date2". There is actually a Jet function that can create this filter for you called NP(DateFilter).
  
  The other problem you have is that you are using NL(First). If you want the NL function to sum records, you must use NL(Sum). So your formula should look something like this:
  
  =NL("Sum","KPI Targets","Daily Call Count Target","sec1_owneridname",C12,"Start Date",NP("DateFilter",D5,F5))
  Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 04, 2013 11:29

  Great thanks for this