Average of Averages in Crosstab

[tld68]

Is it possible to get the total in a crosstab to calculate the average of the average values instead of the average of all records? If so, how? Eg:
2010 1268.40
2011 2096.08
2012 3355.62
Total 2336.30 (I would like this to return 2240.03 instead)
Thanks

 

[CoSpringsGuy]

What version of Crystal?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[tld68]

Sorry - Crystal 2008

 

[Madawc]

I don't think you can do this in a crosstab, where it generates standard logic and you fill in particular values.

You could do a mock-crosstab, separate fields for each year. Get an average and then do an average of that. It might mean using variables. (Use SEARCH for details of mock-crosstabs, if they are new to you.)

Maybe defined as years before the current year, so it adjusts automatically in future years.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[CoSpringsGuy]

Here ya go.... Not a lot of documentation on Embedded formulas for Cross Tabs but this will work great for your situation. I have tested it and received the results you are looking for.

[ul]
[li]right click the white space in top left of crosstab[/li]
[li]select advanced calculation then calculated member[/li]
[li]Click New[/li]
[li]In Description enter AvgOfAvg[/li]
[li]Next to type: Select Row[/li]
[li]Next to Insert Evaluation: Select Before[/li]
[li]Click Edit Insertion Formula and enter[/li]

if CurrentRowIndex = GetNumRows - 1 then true else false[/li]

[li]Save and close formula editor[/li]
[li]Click Edit Header Formula and Enter "Average" (In Quotes)[/li]
[li]Save and close formula editor[/li]
[li]in Value Formulas click the value in that window then click Edit Value Formula[/li]
[li]Enter the following formula[/li]

local numbervar a;[/li]
local numbervar rows := GetNumRows-2;[/li]
local numbervar array values;[/li]
redim values[rows];[/li]
for a := 1 to rows do[/li]
values[a] := GridValueAt (a-1,CurrentColumnIndex , 0);[/li]
average(values)[/li]

[li]Save and close formula editor[/li]
[li]Go to Crosstab expert[/li]
[li]Select Custom Tab and Suppress Column Grand Totals[/li]
[li]Click OK to close Cross-Tab Calculated Member Expert[/li]
[/ul]

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

in the second formula .. remove all instances of [/li]

Thats a tek tips for mating feature I forgot to take out

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[hilfy]

But if you really think about it - an average of a set of averages is really not a meaningful number. Because each internal average can have a different number items included, each has a different "weight" when compared to the whole. That's why Crystal does the total average based on all items instead of averaging the averages.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[CoSpringsGuy]

agreed hilfy
I responded for two reasons..
1 - Thats what they asked for so I assume there is some reason they need it
2 - I have been playing a lot lately with embedded formulas and find them to be very useful. The more people I can get to use them the more good ideas I will see down the road!

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection