Cross Tab Calculations, min,max,average

Hi Mike,

Please follow these steps:

1) While in the Preview Mode, right-click the last date and select Calculated Member > Insert Column. Do this until you see three blank columns with zero values.

2) Right-click the first zero value and select Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;  local numbervar min := GridValueAt(CurrentRowIndex,0,0);  for i := 1 to CurrentColumnIndex-1 do  (      if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex) < min then      (          min := GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex);      )  );  min; 

3) Then, right-click the blank cell beside this value and select Calculated Member > Edit Header formula and write this:

"Minimum"

That's your Minimum Column

4) To find the Maximum follow the same procedure, however use this code:

local numbervar i;  local numbervar max := GridValueAt(CurrentRowIndex,0,0);  for i := 1 to CurrentColumnIndex-1 do  (      if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex) > max then      (          max := GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex);      )  );  max;  

Name this column "Maximum"

5) The last column will be the Average, so get to the Edit Calculation Formula and use this code:

local numbervar i;  local numbervar avg;  local numbervar cnt; //I had to use this variable since currentrowindex was acting funny for some reason  for i := 1 to CurrentColumnIndex-1 do  (      avg := avg + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);      cnt := cnt + 1;  );  (avg+GridValueAt(CurrentRowIndex, 0, CurrentSummaryIndex))/(cnt+1);

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces