show annivesary dates for next quarter 1

chish_nc · Dec 13, 2022

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 · Dec 13, 2022

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

pmax9999 · Dec 13, 2022

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 · Dec 14, 2022

Thanks so much Pete! This worked beautifully

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

show annivesary dates for next quarter 1

chish_nc

MIS

fisheromacs

Technical User

pmax9999

Technical User

chish_nc

MIS

Similar threads

Part and Inventory Search

Sponsor