Running Totals for each Quarter

[shaleen7]

I'm trying to create running total based on the end of year total of the previous year.

For example in Dec 2002 we sold 23 cats. By Mar 2003 we sold an additional 49 cats, thereby making the cumulative total for Mar 2003 72


Dec-02 Mar-03 Jun-03 Sept-03 Dec-03
Cats sold 23 72 82 92 94
Dogs sold 50 70 89 91 97

Total 73 142 171 183 191

Any suggestions? Is this something I have to create with a manual crosstab?

Thank You

 

[lbass]

Yes, you'll need to use a manual crosstab with formulas like this:

{@Dec02}:
if {table.date} in Date(2002, 12, 01) to Date(2002, 12, 31) then 1 else 0

{@Mar03}:
if {table.date} in Date(2002, 12, 01) to Date(2003, 03, 31) then 1 else 0

Place these in the detail section. Insert a group on {table.animaltype}, and then insert summaries on the above formulas and suppress the details.

-LB

 

[shaleen7]

Thanks. I'm new to creating a manual crosstab. This ought to be interesting.

I have one more question. What if I wanted to make the 2002 totals fixed. Using a formula how would I add a fixed amount to the data coming from the database.

 

[lbass]

I'm not sure what the advantage would be. Instead of inserting summaries on the formulas, I think you would have to write summary formulas for each month, such as:

if {table.animaltype} = "Cat" then sum({@Jan03},{table.animaltype}) + 23 else
if {table.animaltype} = "Dog" then sum({@Jan03},{table.animaltype}) + 50 //etc.

-LB