record selector help

[ncchish]

synapsevampire gave me help with this earlier thread767-1214692 for a record selector question. This is in regards to this thread. The user wants to see agents contracted from July of last year until July of this year (starting over every July). If they are contracted in December '06 then they would need to stay on the report through May '07. How can I change the record selector formula to keep the records that may fall off at the end of the year? For now it's getting data from July '05 to July '06 but in July '05 it will start getting data from July '06 to July '07 and I will lose the data it's accumulated so far from the previous year.

Thanks.

if currentdate > cdate(year(currentdate),7,31) then
(
{field.CONTRACT_DTE} > cDate(year(currentdate),7,31)
and
{field.CONTRACT_DTE} <= Date(year(currentdate)+1,07,31)
)
else
if currentdate <= cdate(year(currentdate),7,31) then
(
{field.CONTRACT_DTE} > cDate(year(currentdate)-1,7,31)
and
{field.CONTRACT_DTE} <= Date(year(currentdate),07,31)
)

 

[synapsevampire]

So you DON'T want only each years data?

If you want all data since 05 to always be included, then the record selection is merely:

{field.CONTRACT_DTE} > cDate(2005,7,31)

I suspect that I'm misunderstanding something, you might be better served to provide example data and expected output rather than text descriptions.

-k

 

[ncchish]

Sorry, for my lack of clarity. Here are some examples. The first guy will be tracked from 8/05 to 1/06 - 5 mths. His sales will remain on the report until 12/06. For example 2, her sales need to stay on the report until 2007 because her contract date was so close to end of 2006. So those contracted in '05 need to stay on the report although way through 2006 and possibly '07 depending on when their contract date starts. I hope this makes it more clear.

Thanks.

Ex 1
John Smith
Contract date 8/05
End Date 01/06
Sales 40,000


Ex 2
Jane Smith
Contract Date 12/06
End Date 5/07
Sales 50,000

 

[ncchish]

Also, every July it will start over but we can't lose the data from the previous year if their contract date falls late in the previous year. So, we do start over. I don't think I answered that part of your question.

 

[steve053]

ncchish-

Is there a contract end date field?

If so, you could use the section expert and create a formula under "Supress (No Drill-Down)" to supress when currentdate > {contract_end_date}.

 

[synapsevampire]

Doesn't really clarify, instead of "For example 2, her sales need to stay on the report until 2007 because her contract date was so close to end of 2006", state precisely what the rule is, not that it was so close.

So what are the rules for when data is included? Please don't say something was close, use date fields and logic.

-k

 

[ncchish]

Yes, there is a contract end date which is shown on the examples. 5/07 is the end contract date for ex - 2. The report starts over every July. They track sales for each producer for a period of a year but they only want to track the first 5 months of their production - ie the contract date and end date. So, I need the formula in the record selector to keep up with each year as it changes without hard coding dates. I have a RT formula to keep up with the production accumulated for the 5 mth time period. If someone is contracted at the end of the year they would need to remain on the report until the following year to track their 5 months of production. Thanks for your help.