topic Re: Calculating Gross Earned Premium(GEP) in QlikView

Calculating Gross Earned Premium(GEP)

khan_imran — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sat, 24 Dec 2016 14:32:55 GMT

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

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sat, 24 Dec 2016 14:40:38 GMT

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

GEP = Premium/Active Days*Tenure

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sat, 24 Dec 2016 14:44:35 GMT

Can you check your excel file once more?

I have doubt about your active days calculation !

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sat, 24 Dec 2016 14:52:23 GMT

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

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sat, 24 Dec 2016 14:54:32 GMT

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

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sat, 24 Dec 2016 18:28:31 GMT

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

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sun, 25 Dec 2016 09:00:23 GMT

Try this..

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

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sun, 25 Dec 2016 10:45:34 GMT

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sun, 25 Dec 2016 10:51:07 GMT

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)

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sun, 25 Dec 2016 10:56:46 GMT

Thanks Manish. It is working fine. But there is one more condition which you missed. If you look at the Policy No 1 in screen shot, It reported twice, once on 01/01/2015 and second on 01/05/2016. However the end date remains the same. Here there will be 2 set of GEP, one would be for the previous year entry and second would be for current year entry.

For previous year entry, Active days will be calculated from 01/04/2106 as this is what I have selected. On the other hand, active days will be calculated from 01/05/2016.

Hope I am clear. Do let me know if you have any question.

Regards,

Imran Khan

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sun, 25 Dec 2016 10:59:33 GMT

Provide result you are looking for Policy 1

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sun, 25 Dec 2016 11:08:48 GMT

Absolutely correct Manish. That's wonderful. I got it where I was doing wrong. I was writing as below;

(sum({<ReportNum= {">=$(vStartDate)<=$(vEndDate)"}>}Premium)

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

Thanks very much for your valuable and quick support. I am really delighted to get some knowledge from you.

Wish you a very Merry Christmas and New Year..!!!

Regards,

Imran Khan

Re: Calculating Gross Earned Premium(GEP)

MK_QSL — Sun, 25 Dec 2016 11:11:03 GMT

khan.imran Kindly close the thread by selecting appropriate answer.

Re: Calculating Gross Earned Premium(GEP)

khan_imran — Sun, 25 Dec 2016 11:11:25 GMT

This is what I have written in my application expression:

=sum(aggr(Sum({<POL_REPORTING_DATE_claim1={">=$(new_startup)<=$(NEW_END_DATE_EARNING)"}>}GWP)/

((POL_END_DATE-POL_REPORTING_DATE)+1)

((if(NOT((year(POL_END_DATE)*10000+Month(POL_END_DATE)*100+day(POL_END_DATE))<$(VSTARTUP)

(year(POL_REPORTING_DATE)*10000+Month(POL_REPORTING_DATE)*100+day(POL_REPORTING_DATE))>$(End_date_earning_claim)),

if((

year(POL_END_DATE)*10000+Month(POL_END_DATE)*100+day(POL_END_DATE))>$(End_date_earning_claim),date(makedate(left($(End_date_earning_claim),4),mid($(End_date_earning_claim),5,2),right($(End_date_earning_claim),2)),'DD-MM-YYYY'),

if((

year(POL_END_DATE)*10000+Month(POL_END_DATE)*100+day(POL_END_DATE))<=$(End_date_earning_claim),date(POL_END_DATE)

))

if((

year(POL_REPORTING_DATE)*10000+Month(POL_REPORTING_DATE)*100+day(POL_REPORTING_DATE))<$(VSTARTUP),date(makedate(left($(VSTARTUP),4),mid($(VSTARTUP),5,2),right($(VSTARTUP),2)),'DD-MM-YYYY'),

if((

year(POL_REPORTING_DATE)*10000+Month(POL_REPORTING_DATE)*100+day(POL_REPORTING_DATE))>=$(VSTARTUP),Date(POL_REPORTING_DATE,'DD-MM-YYYY')))

))+1)

,POL_RK,POL_NUM_TXT))