Calculating Gross Earned Premium(GEP)

[khan_imran]

Hello All,

Marry Christmas to everyone who all are connected with Qlik,

Here, I want to calculate Gross earned Premium from Premium field. The logic is mentioned below;

Premium/Tenure*Active Day

Tenure = End Date - Reporting Date+1

Active Days =  it will be based on the selection (Calendar) and I have mentioned the condition below;

Below is the condition to arrive Active Day;

My selection is 01/04/2016(From) - 30/11/2016(to)

- If policy Reporting Date is less than 01/04/2016 then active days will be calculated from 01/04/2016(as per selection), else Reporting Date

- if End Date is greater 30/11/2016 then active days will be calculated till 30/11/2016 else End Date

I am showing Premium only for current year(where my reporting date is as per my selection) but my GEP will consider previous policy as well.

Attached is the data where I have calculated Active Days, Tenure and GEP. I wanted Active Days and GEP will be calculated at expression as both will purely depend on the selection which I make at front end.

Need help to achieve this.

Regards,

Imran Khan

[MK_QSL]

What is the output you are expecting? Can you provide the same in excel file?

[khan_imran]

I have already given in the excel. My output would be GEP.

GEP = Premium/Active Days*Tenure

[MK_QSL]

Can you check your excel file once more?

I have doubt about your active days calculation !

[khan_imran]

I have checked Manish. Its correct. Let me know your questions.

[MK_QSL]

- If policy Reporting Date is less than 01/04/2016 then active days will be calculated from 01/04/2016(as per selection), else Reporting Date

- if End Date is greater 30/11/2016 then active days will be calculated till 30/11/2016 else End Date

Explain this please !

As per above..

If ReportingDate <= vStartDate then something... else something...

So it will never reach to 2nd If...

[khan_imran]

if Reporting Date is <vStartDate then vStartDate, else StartDate. Here my fiscal year starts from 01/04/2016 and ends on 31/03/2017

For example: If any policy has started on 01/01/2016 and ending on 31/12/2016 and I have made a selection as mentioned my original post. In this case, total duration is 366 days however, my financial year starts from 01/04/2016, then 90 days will go in my previous financial year and remaining will come in current financial year. Therefore, my active days till 30/11/2016 will be 244 days. So the calculation will happen the way i mentioned in my original post.

I am struggling to include previous year started policy which are ending in this financial year.

Hope you go my points.

Regards,

Imran Khan

[MK_QSL]

Try this..

(SUM(Premium)/([End Date]-[Reporting Date]))*(IF([End Date] >= $(vEndDate), $(vEndDate),[End Date])-IF([Reporting Date]<$(vStartDate),$(vStartDate),[Reporting Date])+1)

[khan_imran]

[MK_QSL · Accepted solution]

I used this..

IF([End Date] > $(vEndDate), $(vEndDate),[End Date])-IF([Reporting Date]<$(vStartDate),$(vStartDate),[Reporting Date])+1

and

(SUM(Premium)/([End Date]-[Reporting Date]))*(IF([End Date] >= $(vEndDate), $(vEndDate),[End Date])-IF([Reporting Date]<$(vStartDate),$(vStartDate),[Reporting Date])+1)