Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Provide result you are looking for Policy 1
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
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))