How to extract whether it is new business or renewals

[nevilledhamsiri]

Hi, I need to extract whether a particular policy is new or renewal based on the policy number & the risk Month of the policy & the Month in which it was refunded. Eg

policy number              Risk Month         Refunded Month

RA00016001C151          01/10/2016          02/15/2017               policy due to JAN 2016 Refund in Feb 2017

RA00016001D145          02/15/2016          03/21/2017               Policy due to Feb 2016 Refund in Mar 2017

RA0001600TF125          03/10/2016          04/10/2017               Policy due to Mar 2016 Refund in April 2017

RA0001500TF127          04/10/2016          05/10/2017               Policy due to Apr 2015 Refund in May 2017

The Year is the key  in the policy number to decide whether it is a new business or renewals considering the Risk Month as well as Refunded Months. But all the policies which carries 2015 & below such as 2014,2013,2012 & so forth to be renewals. The issue is only policies which were placed in 2016. For all policies placed in 2016, to be decided whether they areRis new or renewals depending on the Rrisk Month & Refunded Month hence Please help me to determine whether they are new or renewals.To make the task simple I may summerise this as below!

Pick the Year from the policy number which is in 6th & 7th character such as 16=2016, 15=2015, Make all policies 2015 & below as Renewals. For policies in 2016, if they are refunded after twelve Months, make it Renewals & other to be New.

Above being a sample few records & the data I am going to load carry may be 100 of records hence please suggest an expression to split them to two dimensions such as new & Renewals.

[mohan_1105]

Hi,

Use mid() to get the year from policy and apply logic in if condition.

=year(makedate('20'&(mid([policy number], 6,2))))

to get the output, use the following if condition:

=if(year(makedate('20'&(mid([policy number], 6,2)))) <= '2015', 'Renewal',

if((num([Refunded Month])-num([Risk Month])) > 365,'Renewal','New'))

Also, make sure you're posting the question in right place. Check other places with this community.

Regards,

Mohan

[nevilledhamsiri · Accepted solution]

Answer received & issue resolved

HI, Mohan,

Thank you very much for your reply which is very easy to understand & apply. I applied it & worked well.Look forward to your support further. I may forward some issues in due course hoping you would help me!

Thanks a lot

Neville

[nevilledhamsiri]

Dear Mohan,

I wrote both in the script &   I get the following error. By having written the 1st on e in the script & 2nd on as an expression nothing went wrong, But I cannot make a  list box for new & renewals. How I will over come this. Also I need to write an expression to sum up the total premiums for the new fields such as new & renewals.

IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]<'2015','RENEWAL',IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]='2017','NEW',IF((Num(CR_MONTH)+365)-Num(RISK_MONTH)>365,'RENEWAL','NEW')))

[nevilledhamsiri]

HI,

Will any body help me on the issue highlighted. Mr Mohan  advised me on this. It worked. I included 1st one in the script to extract the year from the policy number & the if condition was  applied as an expression. Then I was able to classify each policy refunded whether they are new or renewals & they are shown in the pivot table as a field. But for me to have this as a list box & make data appeared in the pivot table is what I want. Please see how I may make this correct.

Thanks

Neville.

"Expect a quick response"

[Anil_Babu_Samineni]

Share your recent expression after created field in script?