conditions according to the year

[capriconuser]

this is the data

M NO	M_Policy	Year
123	111	2018	change
abc		2019	new
223	122-1	2020	change

in this data i want if M_policy not have any policy and year is current year then consider this M NO as "new " so .. if next year come if there is no update on policy abc .. like this abc NO is again assigned and M_Policy have old MNO which is in 2019 so the data would be like this 

M NO	M_Policy	Year
123	111	2018	change
abc		2019	new
abc-1	abc	2020	renewal
223	122-1	2020	change

but if no policy is assigned again in next year then this considered to be as "LOSS" (assume current year is 2020)

M NO	M_Policy	Year
123	111	2018	change
abc		2019	lost
223	122-1	2020	change

so i want to search M No in data like if abc did not assign in upcoming year then this should be considered as LOSS.. 

any help

[Zhandos_Shotan · Accepted solution]

According to example that your send in priv message:

if there is no new number generate in old policy no against polices  then consider as "lost business" other wise above record is renewal business .. so this should be like this 

P No.	Old_Policy	Year
122	111	2018	renweal
122-1	122	2019	renweal
122-2	122-1	2020	renweal
122-3	122-2	2021	renweal
122-4	122-3	2022	renweal
P/03/Me/2018/00002	-	2018	lost
P/03/Me/2018/00003	-	2018	new
P/03/Me/2018/00003-1	00003	2019	renweal
P/03/Me/2018/00003-2	00003-1	2019	renweal
P/03/WC/2019/00001	-	2019	new
P/03/WC/2019/00004	-	2019	new
P/05/WC/2019/00048	0043	2016	renweal
P/11/WC/2019/00011	-	2019	new
P/11/WC/2019/00013	-	2019	new

I created additional PGroup field in script with subfiled function 

SubField([P No.],'-',1) as PGroup

and expression:

if(Len(Old_Policy)>0,
'renew',
if(Year<Year(Today()) and Count(Total<PGroup> [P No.])=1,
'lost',
'new'))

Check the attachment. Is it what you want?

[Brett_Bleess]

Did Zhandos' post get you what you needed?  If so, please consider using the Accept as Solution button to give them credit for the effort and to let other know this was the solution.  If you did something else, please consider posting that and  you can then mark that as the solution...

Regards,
Brett