Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Below expression may help you to achieve expected result,
Sum(Aggr(Sum ([Retention %])*Sum(Profit),LOB))
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.
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.
Can you share your app?
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.
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
Below expression may help you to achieve expected result,
Sum(Aggr(Sum ([Retention %])*Sum(Profit),LOB))
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.