showing average of summarized field in cross tab

[Former Member]

Hello , I have created the following report . I would like to have an average tasks by each name per day.

Is it possible to calculate this, can anyone help? To calculate tasks I use task ID field so in Cross tab expert, after I insert this field I cant use summarized fields, change summary "Average"

[abhilash_kumar · Accepted solution]

Hi Simona,

Do you simply need an Average Column towards the end just like the above screenshot?

If yes, and if you're using CR 2008 or higher then the steps below should work:

1) While in the Preview Mode, right-click the last date and select Calculated Member > Insert Column.

You should see a blank column with zero values inserted

2) Right-click one of the zero values in the column and select Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;   

local numbervar avg;   

local numbervar cnt;

for i := 1 to CurrentColumnIndex-1 do   

(   

    avg := avg + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);   

    cnt := cnt + 1;   

);   

avg/cnt;

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

"Average"

That's it!

Let me know how this goes.

-Abhilash

[Former Member · Accepted solution]

Fantasic! thank you so much Abhilash, the code worked wel and yes the column I need can be at the end, or it doesnt really matter where it is most most important I got the average I needed.

Wish I could learn how to write these type of coding!

[Former Member · Accepted solution]

Hello Abhilash,

I need your help again. The Average column is inserted ok but as I am using date range selection parameters (start and end date), the column is being inserted after the date when I last added a calculated member, it doesnt insert after the last column as selected by ?enddate parameter.

sorry I stated it doesn't matter where this column goes , but I realized it needs to stay always at the end or at the front.

I guess I need to modify Insertion formula? or what do you suggest?

this is current insertion formula:

GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and GridRowColumnValue("@date") = CDateTime (2013, 12, 09, 12, 28, 48)

I was reading your previous answers to others , I tried Maximum, or  to use my "?enddate" parameter entry, but it doesnt work or I don't use it correctly so I gave up searching and trying. I also tried your suggestion to put totals at the right, and then insert a child column. But after I click insert a child column, no empty column is inserted although I can see the entry is inserted in Advanced Calculations.

I am new to using calculated members so need your help .

Simona

[guru-tech-kh60 · Accepted solution]

i use cr 2020 but it's not show the average column sir. please help me

[Aszweezer · Accepted solution]

Hi Abhilash. I'm still new here and would like to know if this function is available on CR version 11? I can't find the calculated member and neither the Average drop down list on Summarized field. Really appreciates for your reply.