How can this calculation be created in Crosstab ?

[pravin.cognos]

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

[pravin.cognos]

Any help !!!

[Grim]

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.

[olivierj]

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 and after more than 40 reader i have no response.  :'(

Good luck.  ;)

[pravin.cognos]

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