Crystal Reports XI - Sum of Cases between date range

Please look below at the attachment to see what I'm trying to do more clearly in case this isn't clear.

PART ONE
Have a report that I'm trying to build that WILL NOT be a CROSS TAB. I want to build a formula that says:
- SUM{tblcases.numbercases} in {tblcases.casedate} in Date(2008,10,01) to (2008,12,31)
-  This would of course be a sum, for a particular hospital, for Q408.

I would then build a separate formula for Q108, Q208, Q308 and so on and drop them in the appropriate spot in the  layout.

PART TWO
I want my current Quarter Total (in the example image USE Q108 as current) to calcualte absolute percentage increase or decrease over the previous quarter.

ANY HELP would be GREAT!!!! Thanks!
Experts-Exchange.jpg
dsabineAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Try this idea

If {tblcases.casedate} in Date(2008,10,01) to (2008,12,31) then
     {tblcases.numbercases}
else
     0

You can then use a summary function on the formula

Another way if you are going to group by the quarter is
Group on the date field
Select QUARTER as the period
You can then summarize the fields in the group footer

mlmcc
0
 
wykabryanConnect With a Mentor Commented:
Well for your Quarter columns I would using running totals.

Set up each like this.
Field to Summarize: numbercases
Summary Type: Sum

Evaluate: Use a formula: This is where you would specify a date range for each quarter

Reset: On change of group: CustomerName. Assuming you are grouping by customer (which you should be otherwise you run the risk of having a dup customer name.)

Click ok. Do this for each. Then your variance column is a formula based on the running totals.
0
 
dsabineAuthor Commented:
mlmcc:

When I tried your solution, the Quarter Columns give me either a 1 or a zero but not a total for each hospital. I am able to do a running total that appears to be correct total for the quarter but I also need totals for each hospital.



wykabryan:

When I try your solution, it gives me totals for the hospitals but only for the date parameter set for the ENTIRE REPORT based on GROUP SELECTION in SELECTION FORMULAS. When I incorporate this, if the report is for all of 2008, the column shows total numbers for 2008 not just the quarter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.