Crosstab Grid - Average Count

[Former Member]

Hi guys, i need some help in Crystal for creating an average of a calculated column in a crosstab.

For better understanding alle the rows are visible, in the columns you can see 2 fields, the coloured one is the value of the db the other one is the

calculated value, in this case if there is a 11 the value should be 0

Formular for the rows:

if GridValueAt(CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex-1) = 11 then
0
else
(GridValueAt(CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex-1))

Now i want to summarize this columns to have the sum of all.

Field: Average_Right

local numbervar i;
local numbervar total;
local numbervar avg;
while i <> currentrowindex do
(
    total := total + tonumber(gridvalueat(i,CurrentColumnIndex,CurrentSummaryIndex));
    i := i + 1;
);
total;

What i need is a average of this total and therefore i need a count of the values which are not like 11.

In the Field "Gesamtdurchschnitt" you can see the average result of CR, what is not correct cause it includes the rows which are 11.

How to include this case into the summarize formular?

Thanks for any hint and help

THX and BR

Rudi

[abhilash_kumar · Accepted solution]

Hi Rudi,

Could you try:

1. local numbervar i; 
2. local numbervar total; 
3. local numbervar avg; 
4. For i:= 0 to currentrowindex -1 do 
5. (
6.     If gridvalueat(i, currentcolumnindex, currentsummaryindex) <> 11 then
7.     avg : = avg + 1;
8.     total := total + tonumber(gridvalueat(i,CurrentColumnIndex,CurrentSummaryIndex)); 
9.     i := i + 1; 
10. ); 
11. total/avg;

[Former Member · Accepted solution]

Hi Abhilash,thx for your reply and your help

It seems to be something wrong with the avg and the total.

Result:

Only AVG as Output: always 1

Only Total as Output:

Do you please have another suggestion?

THX and BR

Rudi

[abhilash_kumar · Accepted solution]

Hi Rudiger,

Which of those rows is the Calculated Members?

Could you attach a copy of this report to the thread or sent it to my e-mail 'with saved data'?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash, i've sent it via mail.

Hope you can find the "error".

THX and BR

Rudi

[abhilash_kumar · Accepted solution]

Rudi,

Could you try:

1. local numbervar i;
2. local numbervar total;
3. local numbervar avg;
4. For i:= 0 to currentrowindex -1 do
5. (
6.     If gridvalueat(i, currentcolumnindex, currentsummaryindex-1) <> 11 then
7.     avg := avg + 1;
8.     total := total + tonumber(gridvalueat(i,CurrentColumnIndex,CurrentSummaryIndex-1));    
9. );
10. total/avg;

-Abhilash

[Former Member · Accepted solution]

Hi Avhilash,

now the Counter for the average seems to be correct, but the sum of the total is still counting the 11.

THX and BR

Rudi

[abhilash_kumar · Accepted solution]

Oh, I didn't realize totals also have to skip the 11s.

Try:

1. local numbervar i;
2. local numbervar total;
3. local numbervar avg;
4. For i:= 0 to currentrowindex -1 do
5. (
6.     If gridvalueat(i, currentcolumnindex, currentsummaryindex-1) <> 11 then
7.   (
8.             avg := avg + 1;
9.             total := total + tonumber(gridvalueat(i,CurrentColumnIndex,CurrentSummaryIndex-1));
10.      )   
11. );
12. total/avg;

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

that's the solution!

THX i will now try to make this average also as a column on the right to get an average on the right side.

THX and BR

Rudi