Pivot Table Aggregation Help

[#1]

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.

Thanks in advance.

[SergeyMak]

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.

Please share your app.

[#3]

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.

[SergeyMak]

Can you share your app?

[#5]

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.

[SergeyMak]

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).

I can't help you without your app

[Anonymous · Accepted solution]

Below expression may help you to achieve expected result,

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

[#8]

Thanks Satyadev

The Aggregate sandwiched between the sums helped and it is working now. Also we cannot use the field names like [Retention %] we need to give the full formula but thats just copy paste.

Thanks once again as you helped solve a major issue.