"New " status according to year

[capriconuser]

i am trying to get status "new" according to this condition 

Parent Policy	From Date	To Date	UW_YEAR	status
MMI/8544	1-Jun-19	1-Jun-18	2019

when policy did not complete 1 year and came first time then this should be considered as "new".Year  based on current year

and year came from "from date" and year which is in "UW Year" should be current year 

i tried this 

For New :

if(POLICY_NO=Previous(POLICY_NO),null,
IF(Policy_Effective_Year=year(Today()) AND UW_YEAR=YEAR(TODAY())) as Status

i want to do this in script .. not in experssion .. so i want "New" keyword in status column ..

but this shows an error 

Aney help

[Arthur_Fong]

Try this:

if((POLICY_NO<>peek(POLICY_NO) and
Policy_Effective_Year=year(Today()) AND UW_YEAR=YEAR(TODAY()),

     'NEW',null()) as Status

[capriconuser]

hi..

Child Policy	Parent Policy	From Date	To Date	UW_YEAR	month diference	New	this is want I want
					-
P/01/CPE/2019/00014	P/01/CPE/2018/00003	01-03-2019	29-02-2020	2019	11	NEW	New
P/01/CPE/2019/00014	P/01/CPE/2018/00003	10-06-2019	29-02-2020	2019	8	NEW	Renew
P/01/CPE/2019/00014	P/01/CPE/2018/00003	10-07-2019	29-02-2020	2019	7	NEW	Renew
P/01/CPE/2019/00014	P/01/CPE/2018/00003	29-07-2019	29-02-2020	2019	7	NEW	Renew
P/01/CPE/2019/00014	P/01/CPE/2018/00003	07-08-2019	29-02-2020	2019	6	NEW	Renew

there is a condition  that if there is same policies then the min from date record should be "new" because this policies came first time and other policies which have same number should be as "renew" as you see in above table.

there is same policy but different from date.. means this policy renew again on 10-6-2019 .. and if last policy did not find in current year then this should be consider as "lost".. but in that case this policy is available in 2019 so this should be as "Renew" if not available in 2019 then should be as "Lost"

how i modify your expression 

[capriconuser]

 @Arthur_Fong 

[Arthur_Fong]

Do you have a sample app?

[capriconuser]

this is the link of qvd file

https://jmp.sh/v/3r84DjYgcYYf29YsM4nc

[StarinieriG]

Hi,

have you already ordered data by child policy, parent policy and from date before using the expression?

[Arthur_Fong]

Try this qvw:

[capriconuser]

hi @Arthur_Fong 

i face issue on renew .. 

like in this data ..  status should not be lost.. because in "lost" case this is depend upon "to date" ..in lost case todate must be in previous year.... so when "to date" (year) is previous year then should be lost other wise renew when child policy is present .

i tried this 

if((Policy_Effective_Year=Year(Today()) and 
UW_YEAR=Year(Today()) AND Month_Difference < 12),'NEW',
If((Len(OLD_POLICY_NUMBER)>0 AND Month_Difference < 12 AND UW_YEAR<YEAR(TODAY())), 'RENEW','LOST' )) AS STATUS

Child Policy	Parent Policy	From Date	minDate	To Date	UW_YEAR	STATUS	month difference
							-
P/01/EWI/2018/001	-	15-07-2018	-	14-12-2023	2018	LOST	65
P/01/EWI/2018/001	-	15-07-2018	-	14-12-2023	2018	LOST	65

[Arthur_Fong]

Can you please reupload your qvd file with this record?