Using Running total in Crosstab formula

[mparshley]

Is this possible? We want to use a crosstab table to report the average purchase of consumables per customer installation.
I can use the running total to accumulate the number of installations on site. I would like to divide the total consumable purchase by the running total of installations by customer.
The columns are the transaction date periods. Data parameters are entered by the user running the report.

Q1 Q2 Q9
Customer 1 # Installs 99 99 99
Total Purchases $9999.00 $9999.00 $999.00
Avg Purch / Install 9999/99 and so on

It is a simple concept but is driving me nuts!

 

[lbass]

Are you using an inserted crosstab? Try inserting a crosstab and add the period as the column, customer as the row, and installs (count or sum?) and sum of purchases as the summary fields. If you then insert purchases again as a third summary, and then select Average as the summary, does this give you the correct figure? If purchases are always associated with installs, then this should work.

Otherwise there is a way to use a running total within a crosstab to accomplish this, but I'm not sure that's necessary in this case.

-LB

 

[mparshley]

No. I have tried this. The installs are instruments that use the consumables. A customer will have one or more. They may buy one instrument in February and three in June, etc. The consumables are used with the instruments and are ordered as they are needed.

We are trying to trend the average consumable purchase per instrument owned over time.

 

[lbass]

There might be an easier way, but the following method (originally described in a Ken Hamady newsletter) works.

Create a formula {@0}:
whilereadingrecords;
0

Add this as your lowest summary in the crosstab expert. Then in preview mode, select the first summary (installs) in an inner cell->right click->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar installs := currentfieldvalue;
false

Then do the same for the second summary (purchases), but using the following formula in the suppression area:

whileprintingrecords;
numbervar purchs := currentfieldvalue;
false

Then select the {@0} summary->format field->common->display string->x+2 and enter:

whileprintingrecords;
numbervar installs;
numbervar purchs;
if installs = 0 then "0" else
totext(purchs/installs, 2)

Repeat these formulas for the total columns and rows, if you want the percentages there as well.

-LB

 

[mparshley]

Thank you. I'll try this early next week.

Martha

 

[mparshley]

This works great! I'll be able to use the method in many situations.
Thanks so much.

 

[mparshley]

I have one more question. In the total section, I need to be able to report just the running total of installations by customer. I can't overcome the fact that it sums the running totals in the sections that come before it.

Thanks again.

Martha

 

[lbass]

I'm not sure I follow. If you have inserted a sum on {table.installs} then the summary for the column total will still be sum of {table.installs}. Don't use the variable for the display at the total level.

I might be missing something, so maybe you should explain whether you formatted the totals the same as you did the inner cells and maybe show a sample of what is going on.

-LB

 

[mparshley]

This is what is happening.

Customer 1 Reagents (Running total of installs) 2
(Total dollars) 2000.
(Avg. per installation) 1000.

Plates (Running total of installs) 2
(Total dollars) 3000.
(Avg. per installation) 1500.

Total (Running total of installs) 4
(Total dollars) 5000.
(Avg. per installation) 1250.

The totals for the customer should be:

(Running total of Installs) 2
(Total dollars) 5000.
(Avg. per installation) 2500.

I've tried using the running total formula field {#installs}
in the running total summary ...suppress->x+2 :

whileprintingrecords;
numbervar installs:= {#Installs};
totext(installs,0)

but the answer is always 1. The calculation of avg per install takes into account whatever figure is in the first summary and works perfectly.

I hope this makes it clearer.

Thank you.

 

[lbass]

I can't tell what you are doing. Please identify the summary you are using on the install field for the inner cells. Is it a sum? A count? Did you change the variables that I suggested using so that they accumulated between rows? If you only did what I suggested, then the total installs would just be the same summary as the inner cells, and would reflect the current value at the total level. If you actually want a different type of summary displayed there, you might be able to use a variable to display something else, but I'd have to understand this better.

-LB

 

[mparshley]

I use Crystal 9. No summary options are offered when a running total is pulled into the summarization section.

The crosstab report trends sales to individual customers. There are two groupings of sales (reagent & plates) and a total.

Each group has 3 summaries: the running total of number of installs at the customer, the total sales and the average sale per install. The groups work well.

For the total section, Crystal is accumulating the running total for each group. What I need is just the running total for the customer, not an accumulation.

Thank you.

Martha

 

[lbass]

Okay, now I see. Honestly, this is the first I've heard you could use a running total as a summary in a crosstab--which was why I was lost. Anyway, you have already set a variable called "installs" equal to the currentfield value in an inner cell. All you need to do then is to select the installs summary in the total line->format field->common->display string->x+2 and enter:

whileprintingrecords;
numbervar installs;
totext(installs,0,"")

-LB