CR XI - New Clients Formula

[ReportDr00]

Hello

I have to determine new clients based on the service date. If the client's minimum service date is greater than 1/1/2010 then they are considered new clients and they do not have any service prior to 1/1/2010

My table has below information
ServiceRowId, ServiceDate, ServiceName,ClientFK,ServiceCode

I cannot use Minimum(ServiceDate,ClientFK)>=1/1/2010 in record selection so i had to pull all service records and use above formula in group but i have over 20 mill records in service table and i am trying to find what would be the best way to do this.

Your feedback is greatly appreciated

 

[lbass]

You could create a SQL expression {%mindt}:

(
select min(`ServiceDate`)
from Table A
where A.`ClientFK` = Table.`ClientFK`
)

Then use a RECORD selection formula of:

{table.date} = {%mindt}

-LB

 

[ReportDr00]

Hey LB

Can you explain your query because i need to know how to add date 1/1/2010 limitation. If the client first got service in year 2010 and no prior services then they are considered new.

Appreciate your feedback

 

[lbass]

Sorry, I meant to say the following for the record selection formula (not group selection):

{%mindt} >= date(2010,1,1)

Or you could use:

{%mindt} >= date(year(currentdate),1,1)

-LB