Retrieve count of claims received within week

[dawn1]

This is driving me nuts! I am trying to create a formula that will only retrieve the number of claims received within a week. I have created a variable for First day of report week and Last day of report week but I can't figure out how to get the count received in between. This is was my last failed attempt which brought back a MULTIVALUE error..can someone help please? I am fairly new to formulas and BO, thanks.
=If([Nature Query].[Dispute Recieved Date]<=[First Day of Report Week])Then([Nature Query].[Count Disputeid])ElseIf([Nature Query].[Dispute Recieved Date]>=[Last Day of Report Week])Then([Nature Query].[Count Disputeid]

 

[fisheromacse]

maybe something like this?
I reversed your <= and >=, removed the extra If's and added an AND.
You could remove the IF/THEN and use it to select records, or leave in place to show the values.


If
[Nature Query].[Dispute Recieved Date]>=[First Day of Report Week]
AND
[Nature Query].[Dispute Recieved Date]<=[Last Day of Report Week]
Then
[Nature Query].[Count Disputeid]

 

[dawn1]

Thanks so much but it didn't work Maybe I have the variables for the First Day of Report Week and Last Day of Report Week incorrect and that is why it can't calculate correctly.
First day of report week is always on a Thursday and the Last day of report week is always Wednesday.
My variable for first day looks like this
=RelativeDate(LastDayOfWeek([Nature Query].[Dispute Recieved Date]);-3)
And Last Day has a +3 instead...not sure if that's correct?

 

[lbass]

Are you using Business Objects or Crystal Reports? This is a Crystal Reports forum.

-LB

 

[fisheromacse]

Can you show an example of how your data currently looks, how you want it to look, any groupings and the contents of the formulas used?

Also, are you trying to select and view only records within the date range? or are you selecting within a broader range for comparison purposes?

 

[dawn1]

Oh, I'm sorry..I didn't notice. I am using Business Objects..I apoligize.

 

[dawn1]

All of the totals should be in first date range 5/20/10 to 5/26/10 but it carries over. I only want to capture records within the date range specified..there are several per year. I know this is for Crystal Reports but you are very helpful, thank you.

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)

 

[lbass]

I don't know how well Crystal Reports solutions translate to BO. There is a forum here called Business Objects Solutions. I think you should re-post there.

-LB