Variance on sub totals from the previous group

[sewilj]

I have got to calculate the difference between group subtotals to display as a monthly variance on the previous month. eg;
sub totals variance
period 1 5
period 2 10 5
period 3 7 -3
period 4 15 8

etc...

the first variance will be zero as there is no previous month.

Anybody able to provide some usefull info that can help?

Thanks

Lewis

Lewis Johnson
Systems Administrator
Birmingham City Council

 

[Madawc]

You're asking Crystal to do something that it's really not good at. Group totals are calculated for that group and then wiped clean.

The only way I can think of within Crystal 8.5 is to accumulate totals separately for each period, as a set of running totals. THen do a formula that compairs the total for one period to the previous one, which would be long-winded but should work.

Maybe one could do something clever with tables, but I have not used tables within Crystal and can't advise.

Madawc Williams
East Anglia, Great Britain

 

[Madawc]

PS. I'd advise you to adjust your ID and not mention the fact that you're working for local government. Lots of programmers don't like government of any sort. And most people don't say who they work for, I myself work for a private-sector concern but I don't see that details are relevant.

Madawc Williams
East Anglia, Great Britain

 

[sewilj]

Madawc

Thanks for that. I might give it a go as the periods will only total 12 so it might not take too long....

I am intrigued by what you said about 'doing something clever with tables'. I appreciate that you have not used them by what do you mean?

Lewis Johnson
Systems Administrator
Birmingham City Council

 

[sewilj]

Madawc

Thanks for the advise

Lewis Johnson
Midlands, United Kingdom

 

[Madawc]

Programming tables might handle period 1, 2, 3 etc., and Crystal has them, but I've never used them.

Madawc Williams
East Anglia, Great Britain

 

[sewilj]

Madawc

Just to let you know that I used the formula below to calculate my variance from the previsou group total where {#Paid State} is the grouped running total (sum of amount from a transaction table).

WhilePrintingRecords;

NumberVar A1;
NumberVar A2;

If {@Period} = "Period 01" then
(
If {@Period} = "Period 01" then (A1 := {#Paid State};0 //sets A1 to the group total but displays ZERO
)
Else
If {@Period} > "Period 01" Then
(
A2:= A1;
A1 := {#Paid State};
A1 - A2;
)


Lewis Johnson
Midlands, United Kingdom