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]
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
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 -->
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
Create the following formulas:
CODE --> {@Next_Quarter_End_Date}
CODE --> {@Age_Next_Querter}
CODE --> {@Noteable_Anniversary}
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