Max a sum expression

[david_pearson]

Hi All

i have the below table, im creating the loss ratio field which is based on the SUM(TOTALCLAIMS)/Sum( EPIREC).

What i want to show is, out of all rows/selections made, what the max of the loss ratio column is in a KPI or Gauge chart via an expression measure.

So far i have the following which works for some cases but not all so im not sure if i have this right. i have put the SUM(TOTALCLAIMS)/Sum( EPIREC) into a variable vLossRatio in the hope to make this easier.

max({<vLossRatio={'$(=Max(vLossRatio))'}>}TOTALCLAIMS/EPIREC)

can someone please help

[MayilVahanan]

HI

Try like below in KPI
Max(Aggr(SUM(TOTALCLAIMS)/Sum( EPIAVIVAREC), DOMICILE, WrittenYear))

[david_pearson]

Hi

it partially works, something is still causing the wrong numbers to appear in certain cases. the below gives me 91.9% based on your suggestion when i was expecting 696.7% based on the table. however for other selections i do get the correct number......im not sure what it is that is causing this difference. 

[MayilVahanan]

HI

How you are getting 2 lines for Year & Domicile for Country - UK & 2014.

[david_pearson]

there is a policy number field but for company security i cannot share. sorry i should have explained this. your code is taking the overall sum of incurred cost for the max year and dividing by sum Premium for the max year. i dont need the year to impact the max ratio. just out of all loss ratios i want to display the max.

***note that there could be the same policy number more than once for different written years

thanks

[david_pearson · Accepted solution]

i have added the policy number field to your code which seems to have sorted it

Max(Aggr(SUM(TOTALCLAIMS)/Sum( EPIREC), DOMICILE, INCEPTION_YR, POLICY_NUM))