Crystal XI - Running Total in a crosstab

[ReillyC]

Hello Experts,
I have a crosstab set up. I think I need a running total in the summarized area. I want to continue to add the number of products added between March and June (106 for Digital) and then between June and Sept. (82 for Digital )


MAR 2005 JUNE 2005 SEPT 2005
DIGITAL 72 (106) 178 (82) 260
ANALOG 65 (74) 139 (75) 214
Crosstab Set up
In the row section, I have a 2 groupings (Digital and Analog) set up on a field called product.
In the column section, I have the date when we purchase the product. I have it set up on a quarterly basis.
In the summarized field I selected count and placed the product id number (it's alphanumeric).

When I created the running total formula all I got was the number one in every cell of the crosstab except the total of course.
Running Total set up
Field to summarize = product id
Evaluate = product 2 groupings (Digital and Analog)
Reset = = product 2 groupings (Digital and Analog)

What am I doing wrong? Any suggestions?

Thanks
R

 

[lbass]

You have only one group (Product), with two instances. Change the evaluation section of the running total to evaluate for every record. Right now, you are telling it to count the product ID on change of group, so there will only be one record that meets that criterion. If you change to count all records within the group, I think you'll get the result you are looking for.

-LB

 

[ReillyC]

Thanks Lbass. I tried what you suggested in my cross tab and got the following results.

MAR 2005 JUNE 2005 SEPT 2005
DIGITAL 72 178 260
ANALOG 65 139 214
The values are correct. Thanks.
However, I'm still not clear on how to get the number of products added between each quarter. I have the values displayed in the parentheses in the table below.
I want (1.)a cumulative total for each quarter but I also would like to display the (2) difference between the two quarters via a running total. Any suggestions. May Thanks


MAR 2005 JUNE 2005 SEPT 2005
DIGITAL 72 (106) 178 (82) 260
ANALOG 65 (74) 139 (75) 214

 

[lbass]

I don't think you can do this in an inserted crosstab. You could create a manual crosstab to accomplish this. You would group on Product, and then create separate running totals for each month in which you would use "evaluate using a fomula" for the evaluation section, e.g.,:

month({table.date}) = 6 and
year({table.date}) = 2005 //for {#June2005}

Place these in the group footer. Then you would create separate formulas for the differences, like:

{#June2005}-{#May2005}

Place these in the group footer also. Drag the groupname into the group footer, and then suppress the group header and details sections.

-LB

 

[lbass]

On second thought, I think the evaluation formula would have to be something like:

{table.date} <= date(2005,06,30)//for {#June2005}

...since you are creating cumulative totals.

-LB

 

[ReillyC]

I'm trying both your suggestions.
I was hoping to use the evaluation formula in the crosstab but the numbers aren't making any sense.
I may have to go with the manual crosstab. I 'll let you know how ti works.

Many Thanks
Cheers
R