Need active customers with no prior year activity 1

[flyingsolo]

I am trying to create a report that pulls Customers who have a current month job that have not had any activity for the prior year. The purpose of the report is to reward salespeople based on a new active job where there have been no jobs in our system for the past year. It doesn't matter if they are a 'brand new' or 'old' customer. I am using MAS200 and I am using a 'status date' populated with the date the job was originated in our system and stored in the job file. That date does not change. To identify New Customers for May, for instance, I would need to identify any jobs originated from May 1 through May 31 that did not also have any jobs originated from May 1, 2009 through April 30th, 2010. I have parameters for entering the date changes each month, but can't get any straightforward selection criteria to work.Thanks in advance for your help. Yes, I am a novice!!

 

[lbass]

Let's assume you have start date and end date parameters for the selected month. Use a selection formula like this:

{table.date} in dateadd("yyyy",-1,{?StartDate}) to {?EndDate}

Then create a formula like this:

//{@InLastYr}:
if {table.date} in dateadd("yyyy",-1,{?StartDate}) to {?StartDate}-1 then 1

Insert a group on CustomerID, and then go to report->selection formula->GROUP and enter:

sum({@InLastYr},{table.customerID}) = 0

Only those with no activity in the previous year, but activity in the selected month, will appear. If you need to do totals across customers, you should use running totals, since the more usual inserted summaries will include non-group selected records.

-LB

 

[flyingsolo]

Just pulled this off the back burner. Your solution worked like a charm!

Thanks!