show annivesary dates for next quarter 1

[chish_nc]

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]

 

[fisheromacs]

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.

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))

 

[pmax9999]

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]

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

[Code {@Noteable_Anniversary}]
If Remainder({@Age_Next_Querter},5) = 0
Then 'Y'
Else 'N'
[/Code]

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

 

[chish_nc]

Thanks so much Pete! This worked beautifully