Print Page - How can this calculation be created in Crosstab ?

Title: How can this calculation be created in Crosstab ?
Post by: pravin.cognos on 21 Mar 2012 12:44:27 AM

Dear Gurus

I have to create Weighted Average calculation in a Cross tab. I have summed the Pool Premiums and got the Total Pool Premium. I also have calculated the Loss Ratio. Not sure how to get Pool Premium for each product type and Total Pool Premium in one data item.

Weighted Average = Sum of (Pool Premium for each product type / Total Pool Premium) X Loss Ratio (for each product type).

We use Relational data. Attached is the look of expected output.

Please help !!

Thanks
Pravin

Title: Re: How can this calculation be created in Crosstab ?
Post by: pravin.cognos on 21 Mar 2012 09:03:22 AM

Any help !!!

Title: Re: How can this calculation be created in Crosstab ?
Post by: Grim on 21 Mar 2012 09:14:47 AM

Quick search in the forums and voila...

http://www.cognoise.com/community/index.php/topic,10465.msg34356.html#msg34356

.. a link with a possible answer.

Title: Re: How can this calculation be created in Crosstab ?
Post by: olivierj on 21 Mar 2012 11:58:18 AM

If I understand, you would like to have only one summary (totalisation) bottom your cross tab ?

I think it's not possible in cross tab. The summary can't be correct.

Do you have testing with aggregate 'aggregate' rather than 'Total' ?

If you using a "List" it's possible with "standalone calculation". It's necessary to create the "Weighted %" measure on Framework Manager and change the propertie "regular aggregate" to "Calculated"...

I have post a topic in this forum http://www.cognoise.com/community/index.php/topic,18802.0.html (http://www.cognoise.com/community/index.php/topic,18802.0.html) and after more than 40 reader i have no response. :'(

Good luck. ;)

Title: Re: How can this calculation be created in Crosstab ?
Post by: pravin.cognos on 22 Mar 2012 12:29:08 AM

Dear olivierj

Thanks for your reply. I have seen your post which is nearer to my current scenario. As there were no replies, I thought of trying my luck ;)

Dear Grim

Thanks for the reply and link :)

It shows the division of Total (x)/Total (Y). But below is the scenario we actually need. I have attached the formula for this in my initial post.

   Total Premium   Pool Premium   Loss Ratio
Accident   $1,000,000   $900,000    75.00%
Disability   $1,000,000   $850,000     82.00%
Life   $1,000,000   $750,000     90.00%
Medical   $1,000,000   $950,000     79.00%
Total   $5,000,000   $4,325,000
Sum (Weighted Average)           64.68%

Calculation for Accident weighted average = (Accident Pool Premium/Total Pool Premium) * Loss Ratio

Like this we have to calculate for Disability and other 2, then sum that value to show the Sum of Weighted Average column i.e 64.68%.

Challenge for me here is, I am not sure how to divide the individual weighted averages with the Total of them. Also they must not be shown in the crosstab only sum value must be shown as mentioned above.

Thanks
Pravin

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: pravin.cognos on 21 Mar 2012 12:44:27 AM