×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

show annivesary dates for next quarter

thread767-1819754

show annivesary dates for next quarter

show annivesary dates for next quarter

(OP)
I have a report that the user wants to see the service dates for those who meet their 5, 10, 15, 20, etc service date for the next quarter. So, it's 4th quarter now but they want to see who will have a milestone service anniversary next Jan-March.

I have this in the r/s for the quarter but having trouble with getting just those who will meet the 5, 10, 15 year in that quarter. I am getting everyone in the Jan-March and can't figure out a formula just for just the milestone dates. Any help is appreciated - using Crystal Reports 2016.

if DatePart("q", currentdate) = 1 then
month({HIER.CONTRACT_DTE}) in [4,5,6] else
if DatePart("q", currentdate) = 2 then
month({HIER.CONTRACT_DTE}) in [7,8,9] else
if DatePart("q", currentdate) = 3 then
month({HIER.CONTRACT_DTE}) in [10,11,12] else
if DatePart("q", currentdate) = 4 then
month({HIER.CONTRACT_DTE}) in [1,2,3]

RE: show annivesary dates for next quarter

in english, the below is what you want...i think.

If hiredate is in apr mar june then Q1
else
if hiredate is in july aug sept then Q2
else
if hiredate in oct nov dec then Q3
else
if hiredate in Jan feb mar then Q4


If the above is correct, then you would want change your how your formula evaluates.

CODE --> 

if month({HIER.CONTRACT_DTE}) in [4,5,6] then 1 else
if month({HIER.CONTRACT_DTE}) in [7,8,9] then 2 else
if month({HIER.CONTRACT_DTE}) in [10,11,12] then 3 else
if month({HIER.CONTRACT_DTE}) in [1,2,3] then 4

then you would need another formula to evaluate if the returned value matched the current quarter...the example below could be used to suppress unwanted rows, or with a little modification could be used in the Select expert to limit the rows returned (a better approach generally speaking)
ie: NOT({@QtrFormula} = DatePart('q',currentdate))

RE: show annivesary dates for next quarter

Try this:

Create the following formulas:

CODE --> {@Next_Quarter_End_Date}

If      Month(Today) in [1 to 3]
Then    Date(Year(Today), 6, 30)
Else
If      Month(Today) in [4 to 6]
Then    Date(Year(Today), 9, 30)
Else
If      Month(Today) in [7 to 9]
Then    Date(Year(Today), 12, 31)
Else
If      Month(Today) in [10 to 12]
Then    Date(Year(Today) + 1, 3, 31)

CODE --> {@Age_Next_Querter}

Truncate(DateDiff('d', {HIER.CONTRACT_DTE}, {@Next_Quarter_End_Date})/365.25, 0)

CODE --> {@Noteable_Anniversary}

If      Remainder({@Age_Next_Querter},5) = 0
Then    'Y'
Else    'N'

Any record where the final formula {@Noteable_Anniversary} = 'Y' will be the ones you want to report on, so suppress those where the result of the formula = 'N'.

Hope this helps.

Regards, Pete

RE: show annivesary dates for next quarter

(OP)
Thanks so much Pete! This worked beautifully :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close