7 Replies Latest reply: Jun 9, 2014 2:19 AM by Hatim Maskawala

# Pivot Table Aggregation Help

I am creating the following Pivot Table. The data contains multiple policies whose Premiums, Earned Premiums, etc are being calculated by normal Sum formula in the pivot. If you look at the last column Profit Retained the sub-total should be a sum of the values above. Instead its is calculating it based the sub-total row.

Retention % = Sum (Retained Premiums) / Sum (Premiums)

Profit = Sum (Earned Premium) - Sum (Incurred Claims)

Profit Retained = [Profit] * [Retention %]

For individual rows the profit retained is coming correctly. However, for the sub-total I do not want it to multiply but just take total of the already calculated values. Right now it is showing 3730390 (i.e. 47.2% * 7904837) however, if you total the individual rows it comes to -1922125.

 RA_Year LOB Premiums Earned Premium Incurred Claims Retention % Profit Profit Retained 2010 CARGO 106460 106460 0 66.4% 82509 54760 CASUALTY 745953 745953 359832 99.4% 200218 198937 ENGINEERING & CONSTRUCTION 7454665 7454665 819522 36.3% 4964344 1803044 MARINE HULL 509154 509154 0 29.6% 394594 116941 MEDICAL 6468720 6468720 11042546 100.0% -5708491 -5708491 MOTOR 795587 795587 133776 100.0% 483232 483232 PERSONAL ACCIDENT 2896 2896 0 100.0% 2245 2245 PROPERTY 10417252 10417252 326752 15.1% 7486187 1127206 Total 26500687 26500687 12682428 47.2% 7904837 3730390

Can you please guide me as to what I am doing wrong here.

• ###### Re: Pivot Table Aggregation Help

Hi

Your wrote that Profit = Sum (Earned Premium) - Sum (Incurred Claims)

I see that the first row has wrong value of profit, because Earned Premium=106460, Incurred Claims = 0, but Profit is not equal 106460. So something wrong.

• ###### Re: Pivot Table Aggregation Help

Actually Profit has many other elements in it. I wanted to keep it simple that is why I only wrote 2 items.

The actual formula is - Profit = sum(PREMIUM *  \$(vEarned_Factor) * .925) - (SUM(GROSS_CLAIM_PAID) + sum(OSLR) + sum(\$(vIBNR))) - SUM(COMMISSION *  \$(vEarned_Factor)) + SUM(RI_COMMISSION *  \$(vEarned_Factor))

The profit column is working correctly.

I am having issues in the sub-total for Profit Retained. I do not want it to calculate from the sub-total row but from base data as there will be differences due to the weightages.

• ###### Re: Pivot Table Aggregation Help

Can you share your app?

• ###### Re: Re: Pivot Table Aggregation Help

Due to Data Protection issues I cannot upload the QVW file. However, I attach an excel file which would help in getting my point across.

Columns A to K are coming from the Pivot Table which I have created. Column M recalculates the Column J which is ok. And Column N recacluates Column K as Qlikview is doing and Column O shows what I want. The difference is basically in the way it is handling sub-totals. Due to the mix of business, recalculating at the sub-total level gives a wrong answer.

Hope this clarifies.

Thanks.

• ###### Re: Re: Pivot Table Aggregation Help

Unfortunately excel file doesn't help.

I need your app to see formulas and data model.

For security reasons you can Reduce the data (File\Reduce data) and/or scramble data (Settings\Document Properties\Scrambling then select needed field and click Scramble).

• ###### Re: Pivot Table Aggregation Help

Below expression may help you to achieve expected result,

Sum(Aggr(Sum ([Retention %])*Sum(Profit),LOB))