Delay intimation of claims in (months)

[nevilledhamsiri]

To calculate the above in the script, I seek your opinion!. This is the difference between (Reported_Date -Loss-Date). I need this to be calculated in Months. The excel format too attached for your reference.

Thanks

Neville

CLAIM_DATA:

LOAD RNUM,

     BRANCH,

     REPORTED_DATE,

     LOSS_DATE,

     CLASS_CODE,

     PRD_CODE,

     POLICY_NO,

     NAME_OF_THE_INSURED,

     CLAIM_NO,

     RISK,

     BALANCE_BF,

     MONTHS_PROVISION,

     PAID_AMOUNT,

     UNDER_OVER_PROVISION,

     BALANCE_CF,

     PAID_DATE,

     CLOSED_DATE,

     CLOSED_STAUS,

     ADVANCED_PAYMENT,

     REMARKS,

     NO_OF_FINAL_PAYMENTS_IN,

     NO_OF_ADVANCED_PAYMENTS,

     PREV_MON_INT_PROV,

     REASON_OUTSTANDING,

     ME_CODE,

     CONV_CODE,

     CAUSE_OF_LOSS,

     [Paid after Recovery],

     [Reported  after Recovery],

     [Paid after Recovery(2)],

     [Reported  after Recovery(2)],

     [Reported Year]

FROM

[..\CLAIM_DATA.xlsx]

(ooxml, embedded labels, table is SOURCE_DATA);

LET VDelay=REPORTED_DATE-LOSS_DATE;

LOAD  $(Delay) AS dates_delay;

RNUM	BRANCH	REPORTED_DATE	LOSS_DATE	DELAY	CLASS_CODE	PRD_CODE	POLICY_NO	NAME_OF_THE_INSURED	CLAIM_NO	RISK	BALANCE_BF	MONTHS_PROVISION	PAID_AMOUNT	UNDER_OVER_PROVISION	BALANCE_CF	PAID_DATE
1	RA00	7-Oct-08	7-Oct-08	0	MC	1D	RA00071D002603	MR. B.M FAZAR	CLRA1D09000037	47-7135	0	0	200,000.00	200,000.00	0	11-Jan-17
2	RA00	13-May-15	12-May-15	0	MC	1A	RA00141A0002101	MRS. M.V.U.S. VITHARANA.	CLRA001A15000103	32-4308	0	0	6,000.00	6,000.00	0	6-Mar-17
3	RA00	26-Oct-15	23-Oct-15	0.1	MC	1A	RA00151A0002429	MR. P.K.U. KARUNARATHNA	CLRA001A15000234	SGCAJ9410	0	0	8,500.00	8,500.00	0	17-Feb-17
4	RA00	21-Dec-15	18-Dec-15	0.1	MC	1A	RA00151A0002396	MR. P.G.G. HARISHCHANDRA	CLRA001A15000287	SGCAI1141	0	0	0	100,000.00	100,000.00	-
5	RA00	25-Jan-16	22-Jan-16	0.1	MC	1A	RA00151A0002437	MRS. W.D.J.C. WELIGAMA	CLRA001A16000022	SGCAB4005	0	0	14,350.00	14,350.00	0	4-Apr-17
6	RA00	3-Feb-16	2-Feb-16	0	MC	1B	RA00131B0005785	MR. Y.M. AYUPALA	CLRA001B16000003	WPKX0539	0	0	23,250.00	23,250.00	0	9-Jan-17
7	RA00	1-Mar-16	20-Feb-16	0.3	MC	1A	RA00151A0002351	MRS. A.S.S AMARASINGHE	CLRA001A16000064	WPKJ6692	######	0	0	20,500.00	0	-
8	RA00	16-Mar-16	15-Mar-16	0	MC	1D	RA00141D0003663	MR. K.M. ABERUWAN.	CLRA001D16000020	SGGG4624	33,850.00	0	0	-33,850.00	0	-
9	RA00	17-Mar-16	16-Mar-16	0	MC	1A	RA00161A0002564	MR. I.S. WAKKUMBURA	CLRA001A16000082	SGKL0659	0	0	8,300.00	8,300.00	0	15-Feb-17
10	RA00	1-Apr-16	1-Apr-16	0	MC	1A	RA00_MOT_SAL_LOT_REF_NO	RA00_MOT_SAL_LOT_REF_NO	RA00_MOT_SAL_LOT_REF_NO	RA00_MOT_SAL_LOT_REF_NO	1	0	0	0	1	-
11	RA00	27-Apr-16	15-Apr-16	0.4	M4	4X	RA00144X0000136	MR. J.A. ANURA JAYAWARDENA	CLRA004X16000020	SGAAC1019	0	0	17,600.00	17,600.00	0	20-Feb-17
12	RA00	6-May-16	1-May-16	0.2	MC	1X	RA00141X0000837	MR. B.A.D. NUWAN CHAMARA	CLRA001X16000071	SGYI3748	######	0	350,000.00	150,000.00	0	14-Feb-17
13	RA00	10-May-16	10-May-16	0	TC	TC	RA0013TC0002385	MRS. M.D. MAHATHANTHILA	CLRA00TC16000018	GROCERY	0	0	6,818.00	6,818.00	0	######
14	RA00	16-May-16	12-May-16	0.1	MC	1A	RA00151A0002472	MS. THE OCEAN COLOMBO (PVT) LTD	CLRA001A16000130	WPCAJ6749	8,500.00	0	0	-8,500.00	0	-
15	RA00	31-May-16	24-Feb-16	3.2	MC	1X	RA00141X0001619	R.H.A. PODIMENIKE	CLRA001X16000083	SGGU8477	0	0	175,000.00	175,000.00	0	27-Feb-17
16	RA00	8-Jun-16	16-May-16	0.8	MC	1A	RA00151A0002442	MR. K.M.P. KARUNAKALAGE	CLRA001A16000150	SGGB6389	0	0	111,900.00	111,900.00	0	10-Jan-17
17	RA00	13-Jun-16	11-Jun-16	0.1	MC	1C	RA00141C0002888	MR. R.C. KANDANGODAGE	CLRA001C16000049	WPLH6686	######	0	0	-250,000.00	0	-

[effinty2112]

Hi Neville,

Try:

Data:

LOAD

RNUM,

     REPORTED_DATE,

     LOSS_DATE,

     12*(Year(REPORTED_DATE)- Year(LOSS_DATE)) + Month(REPORTED_DATE)- Month(LOSS_DATE) as DiffMonth,

     12*(Year(REPORTED_DATE)- Year(LOSS_DATE)) + Month(REPORTED_DATE)- Month(LOSS_DATE)

     -if(Month(REPORTED_DATE) > Month(LOSS_DATE) AND Day(REPORTED_DATE) <= Day(LOSS_DATE),1,0)    as DiffMonth1

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

There are two interpretations of the difference in month. Consider these rows.

RNUM	REPORTED_DATE	LOSS_DATE	DiffMonth	DiffMonth1
7	01/03/2016	20/02/2016	1	0
15	31/05/2016	24/02/2016	3	3
16	08/06/2016	16/05/2016	1	0

RNUM 7 & 15 could be said to have a date difference of one month since the reported date is in the month following the loss date. On the other hand there is less than a full month between the dates so Diffmonth1 returns 0.

You could use either of the expressions in the UI if you prefer.

Good luck

Andrew

[nevilledhamsiri]

Dear Walker,

Thanks for the best application provided.

Neville