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
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)
What is the output you are expecting? Can you provide the same in excel file?
I have already given in the excel. My output would be GEP.
GEP = Premium/Active Days*Tenure
Can you check your excel file once more?
I have doubt about your active days calculation !
I have checked Manish. Its correct. Let me know your questions.
- 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...
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
Try this..
(SUM(Premium)/([End Date]-[Reporting Date]))*(IF([End Date] >= $(vEndDate), $(vEndDate),[End Date])-IF([Reporting Date]<$(vStartDate),$(vStartDate),[Reporting Date])+1)
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)