Average % to compute including Null Values as well

[Ann]

A simple requirement, somehow not able to fix it:

Row                c1        c2         c3         c5         c5        Grand Total
A1                               15.0%   84.6%   0.5%   0.0%   100.0%
A2                   1.6%   31.4%   63.2%   3.6%   0.3%   100.0%
A3                           0.0%   100.0%   0.0%   0.0%   100.0%
A4                  2.8%   43.5%   53.4%   0.4%   0.0%   100.0%
A5                 3.2%   35.7%   49.8%   8.2%   3.1%   100.0%
A6                 9.4%   24.5%   60.5%   3.7%   1.8%   100.0%
A7                             87.7%   12.3%   0.0%   0.0%   100.0%
Grand Total   2.9%   36.4%   55.7%   3.8%   1.2%   100.0%

Required % 2.9  (1.6+2.8+3.2+9.4)/7  in first column (Grand Total), I am getting 4.24  (1.6+2.8+3.2+9.4)/4 in my crosstab.
how to fix this?


Any clue would be appreciated

[BigChris]

That sets my spidey sense tingling...adding up percentages is potentially fraught with perils. Before I start trying to find a solution are you absolutely sure it's the right thing to do??

[Ann]

I actually wanted average percentage. but while computing the percentage i want to make use of thos  which do not have a value

[Cognos.Developer]

Hi,

Please try with Solve order function, it may help us.


Thank YouHelp_Cognos

[jmills]

If you replace your Null's to zero, they will be included in the aggregates.  Per the thread, aggregated percentages may not make much sense.

[sunny bachan prasad]

Take the aggregate function it will work.

[Ann]

Hi All,

I fixed this.

Created a seperate dataitem computing the Average % the way i wanted and appended the same as a row in crosstab.

Thanks!

[jai1970]

Hi Ann

I also have the same issue, please let me know, how you solved it creating another data item.  In your Rows , I got months and those will be based on the filter selection. If the user select 3 months, It should average for 3 months, even they have null columns. So, average should be based on the selected months.

Any idea, please can you explain in details how you have achieved.

Regards
Jai