Running total that evaluate on change of group AND formula

[Budjette]

Hi!

I use CR 8.5 and SQL database. I have a running total that summarize a field. I need to evaluate it on a change of month (group #3) and reinitialize it on a change of year (group #2). I have another group (group #1 : currency) that I need to do another calculation. Let me explain. I have the month of january and february of 2003 and 2004 so 4 numbers that I need to count and it should go like this:
Group by month (group #2)
200301 : 1.2
200302 : 1.3
200401 : 1.6
200402 : 1.4

I need to sum 2003 : 2.5 and sum 2004 : 3.0. After that, I need to take the result of 2004 minus the result of 2003 : 3.0 - 2.5 = 0.5. This result needs to be in the group #1. I was thinking of a running total that evaluate on a change of group AND on a formula but that is not possible. Anybody have any suggestions?

Thanks in advance,

Bud

 

[lbass]

You say in one place that Group 2 is year and then later that it is month. But, if we assume that you want the calculation in the currency group footer, then do two running totals like this:

{#2003}: Select {table.amt}, sum, evaluate based on a formula:
year({table.date}) = 2003 //or if the date field is a string, use:

left({table.date},4) = "2003"

Reset on change of group (currency)

{#2004}: Same as previously, except use

year({table.date}) = 2004

This will allow the results to be carried into the group footer (currency) for each running total. Then create a formula {@diff}:

{#2004}-{#2003}

Place this in the currency group footer.

-LB

 

[Budjette]

Thanks,

I'll try that, hope this works.

Bud