I am trying to run a report which captures all claims received within a week in a "to" "from" format. The First day of my report week is always on Thursday and the Last day of report week is always the next Wednes. I cannot figure out a formula to count all the claims received within the date range I specify. The example I have attached should only be pulling claims received from 5/20/10 to 5/26/10 but the totals carry over into the next date range. Can anyone help?
All of the totals should be in first date range but it carries over.
Drop Dates Due Date Received in Week
5/20/10 to 5/26/2010 7/13/2010 31
5/27/10 to 6/2/2010 7/20/2010 266
Drop Date Formula:
=[First Day of Report Week] + "to"+ [Last Day of Report Week]
Received in Week:
=[Nature Query].[Count Disputeid]
Variable Fromula:
First Day of Report Week:
=RelativeDate(LastDayOfWeek([Nature Query].[Dispute Recieved Date]);-3)
Last Day of Report Week:
=RelativeDate(LastDayOfWeek([Nature Query].[Dispute Recieved Date]);+3)
All of the totals should be in first date range but it carries over.
Drop Dates Due Date Received in Week
5/20/10 to 5/26/2010 7/13/2010 31
5/27/10 to 6/2/2010 7/20/2010 266
Drop Date Formula:
=[First Day of Report Week] + "to"+ [Last Day of Report Week]
Received in Week:
=[Nature Query].[Count Disputeid]
Variable Fromula:
First Day of Report Week:
=RelativeDate(LastDayOfWeek([Nature Query].[Dispute Recieved Date]);-3)
Last Day of Report Week:
=RelativeDate(LastDayOfWeek([Nature Query].[Dispute Recieved Date]);+3)