COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: Rolldice on 04 Feb 2008 07:28:12 PM

Title: Weighted Averages
Post by: Rolldice on 04 Feb 2008 07:28:12 PM
Hello,

I have a crosstab with calulated fields as rows (reasons).

The column is weekly count data based on a dimensaional data source devived for a prompt.  I would like to have a weighted average column as well.  But I'm having no luck:

The average wizard doesn't return any values..  My attempt to write a calulated field to work out the avg isn't working either???

example:

reasons   Week End    Weight Avg

'ABC'          20               10%
'BCD'          60               30%
'EFE'          120              60%
----------------------------------
totals         200


- Can anyone help?

Cheers ??? ???
Title: Re: Weighted Averages
Post by: Suraj on 05 Feb 2008 09:29:11 AM
Weight Avg = total (week end for reasons) / total (week end for report)
and format as %
Title: Re: Weighted Averages
Post by: Rolldice on 07 Feb 2008 11:34:19 PM
Suraj,

Thanks for your response.. The issue that I'm having is that my reasons 'ABC' etc.. are sets of reasons, from the reason dimension.

I believe that is the issue - I'm still not able to get the weighted average from the sets of reasons in my crosstab.  Is there a way of calulating the average of the sets of reasons?>>

Thanks,
Title: Re: Weighted Averages
Post by: Suraj on 08 Feb 2008 10:25:14 AM
try the same calculation in a list report.
if you see data there but not in crosstab, play with Aggregate/Rollup Aggregate properties; change to calculated...etc..
Title: Re: Weighted Averages
Post by: Rolldice on 10 Feb 2008 11:23:17 PM
Suraj,

Thanks for your help - I finally got it!

I created a Member set for my Reasons, for the underling query from the dimensional data source.

Then used:

[measure] / aggregate([measure] within set [Reasons])

Cheers!!