Crystal 2013 Group Average and Total Average

[jchewsmith]

I have data in a table that is grouped by month then order id, then has line item detail.

I am trying to average my order id because the line item detail is always the same on the field that I want to average and I need to include each line for other reasons.
I am currently trying to take the average of each order group because it will always equal the line average since they are all the same
I then want to get an average of the group average not of all lines.

eg.
order line# Date hrs
123 1 1/1/15 2.5
123 2 1/1/15 2.5
average 2.5

345 1 2/15/15 3.5
345 2 2/15/15 3.5
345 3 2/15/15 3.5
average 3.5

Total Average 3 not 3.1 which is the average if you use every line instead of each group

 

[pmax9999]

Create the following formula and place it in the Date Group Header or Footer.

[Code {@AVE_Month}]
WhilePrintingRecords;
Global NumberVar CNT := CNT + 1;
Global NumberVar AVE := AVE + Average ({Table.Hrs}, {Table.Date}, "monthly");

Average ({Table.Hrs}, {Table.Date}, "monthly");
[/Code]

Create the following formula and place it in the Report Footer.

[Code {@AVE_All}]
WhilePrintingRecords;
Global NumberVar AVE;
Global NumberVar CNT;

AVE/CNT
[/Code]

Hope this helps.

Cheers
Pete.

 

[pmax9999]

You could also also use a Running Total: Evaluate: on change of group; Reset: never

The RT approach will work as long as all of the detail lines have identical values for HRS. Personally I find RTs a bit limiting and therefore they are rarely my default position but will work in the situation you describe.

My earlier approach using variables is probably more versatile and worth the effort to understand it.

Cheers
Pete.

 

[SkipVought]

You do realize that the average of averages is a meaningless abstract number, that, at best can be described as a statistician's stratagem and at worse a schoolboy's blunder.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Correct Skip. But this is a forum dealing with Crystal Reports formulas, not mathematical theory.

 

[SkipVought]

I wouldn't want to instruct a member in a method for cutting off the limb upon which he is relying to move his/her world or help them fashion a noose, although I'm told that you can get used to hanging if you hang long enough.

I strive not merely to give answers but solutions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]