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

[khan_imran]

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

[MK_QSL]

Provide result you are looking for Policy 1

[khan_imran]

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

[MK_QSL]

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

[khan_imran]

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)

or

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