Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to add 3 months charges in group footer

Status
Not open for further replies.

azoe

Technical User
Feb 21, 2005
175
US
SQL database, CR 11
I need to add and divide a couple of numbers in a group footer but can't even get the first step done.

In the report I group by year and then by month.
I use a running total to get the total charges for the month in group footer 2.

Then I tried creating this formula:
{#Charges} + Previous({#Charges})

But Crystal highlights the 2nd {#Charges} after the plus sign and says:

This field has no Previous or Next value.

Any idea how I can add 3 months charges together? (The user picks the beginning and ending date - I want to add the 3 months on a rolling basis: Jan + Feb + Mar, then Feb + Mar + Apr, etc)

Thank you.
 
So I guess what you're saying is that you want quarterly sums in the year footer.

Running Totals aren't available after the ffact, hence the no go on the previous.

One option is to use the insert of the date as Quarter.

A little clrification would be useful, give an example of the dates that are entered and the resulting output.

Fo instance if they enter a one month range are you intending to show the last year including any qurters within, and if it's January, what quarters are you intending.

In general it's best to post example data and expected output.

-k
 
Dates the user might enter (any dates they want):
Start: March 1, 2005 End: April 1, 2006

Output:
Charges 3 months of charges
2005
March 100.00 Nothing since Feb and Mar not chosen
April 50.00 Nothing since March not chosen
May 200.00 350.00
June 130.00 380.00
July 40.00 370.00
etc.

For every row the figure in the 3rd column is that months charges plus the previous two months charges.

No matter what dates they enter it starts totaling on the 3rd month. What they really want is payments plus adjustments divided by charges but I thought I should start with one thing at a time like trying to add the charges.

Thank you.
 
I'm confused by your example. Did you change the parameter start and end dates from March 1 and April 1?

-LB
 
Oh my goodness, I sure did. I decided to change it to make it more clear that calendar quarters may not always be appropriate. Sorry.

Output:
Charges 3 months of charges
2005
March 100.00 Nothing since Jan and Feb not chosen
April 50.00 Nothing since Feb not chosen
May 200.00 350.00
June 130.00 380.00
July 40.00 370.00
etc.
 
Well, what WAS your parameter selection for your example? What I'm not sure about is how the parameters relate to the three month period. What if they chose May 1 to July 15? What would you expect to see for results? Or do you always want whole months? Which three months? If you have a parameter for May 1 to July 15, would you expect data for May to October (May 1 to October 15)?

-LB
 
The example was Start:March 1, 2005 End: Mar 30, 2006

They would choose whole months. Whatever dates they choose the 3 months are inside of that. If they choose May 1 to July 31 they will only have one 3 month total.

2006 ChargeAmt 3 months of Charges
May 200.00 nothing
June 130.00 nothing
July 40.00 370.00

If they pick Feb 2006 thru June 2006:
2006 ChargeAmt 3 months of Charges
Feb 300.00 nothing
Mar 430.00 nothing
Apr 100.00 830.00
May 200.00 730.00
June 130.00 430.00

Ultimately I'm supposed to (in the same manner) add 3 months of payments with 3 months of adjustments and divide that total by this 3 months of charges divided by 3 (in case that makes a difference in how I do this). The NetAvg I can do but I don't know how to do the 3 months Average.

So for Feb 2006 thru June 2006:
2006 Pmt Adj ChargeAmt NetAvg 3 months Average
Feb 50.00 25.00 300.00 .25 nothing
Mar 70.00 10.00 430.00 .19 nothing
Apr 55.00 15.00 100.00 .7 .38
May 60.00 20.00 200.00 .4 .43
June 33.00 12.00 130.00 .35 .48

75 / 300 = .25

I got the .38 by adding (.25 + .19 + .7)/3

I hope that is more clear. Thank you.
 
Try adapting bdreed's formula in thread767-704190.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top