Set Expression Problem

[tamilarasu]

Hi,

I am trying to get count of prefixes based on few criteria. I have tried the below expression and getting the desired result but additional unwanted lines.

P.DBI={">$(=Date(Yearend(Today(),5),'YYYY-MM-DD'))"} - Here the criteria is .BPI field should greater than 5 years from today (2015-12-31)

P.DBI-={'9999*'} - I just exclude the date which starts with 9999.

Count(DISTINCT {1}{<P.KR={'1','2'},P.KSL={'ZD'},P.DBI={">$(=Date(Yearend(Today(),5),'YYYY-MM-DD'))"}, P.DBI-={'9999*'}>

+

<P.KR={'1','2'},P.KSL={'ZD'},P.DAB-={'20*','19*','199*'}>}M.PREFIX)

But I don't know why I am getting results in the year 2015. I have tried to the same code by excluding the condition P.DBI-={'9999*'} and the result is fine but when I add this P.DBI-={'9999*'}, getting extra line.

What could be the reason.?

[swuehl · Accepted solution]

I believe you can't modify the same field twice in a single set modifier, ie. assigning two element lists to P.DBI in your first set modifier. I believe QV will take just the second one.

If you want to take 9999 out, try

Count(DISTINCT {1}{<P.KR={'1','2'},P.KSL={'ZD'},P.DBI={">$(=Date(Yearend(Today(),5),'YYYY-MM-DD'))<=$(=Date(Year(9999)))"} >

+

<P.KR={'1','2'},P.KSL={'ZD'},P.DAB-={'20*','19*','199*'}>}M.PREFIX)

[tamilarasu]

Thank you for your reply. If I use the above expression, the expression $(=Date(Year(9999))) will return the date as 04-10-1905. I want to exclude the dates end with 9999. 

[swuehl]

Right, my mistake.

$(=Date(MakeDate(9999),'YYYY-MM-DD'))

but I think even

$(=MakeDate(9999)) should be enough within your advanced search.

[tamilarasu]

Swuehl, 

If we use above expression, the date will be 999-01-01. I have changed the code to $(=Date(MakeDate(9999,12,31),'YYYY-MM-DD')) and its working fine. If possible edit the code which I am going to mark it as correct. It might be useful to someone in future. Thanks a lot for your help.

and cool weekend!!