Need help getting oldest anniversary date to show
Need help getting oldest anniversary date to show
(OP)
I’m working on a report using CR 2016. I'm having an issue getting the original anniversary date for an agent. An agent can have multiple agent numbers but they only want to see the oldest anniversary date. So, you might have this scenario:
So, they would want to see the anniversary date of 12/27/90 in 2 more years because the report only shows agents in milestones of 5-year increments. It’s showing the other 2 milestone years but it’s not his original ann date and they don’t want to see him for 2 more years, so he shouldn’t be on the report at all until he reaches his 35 year with the oldest date. The report is working fine getting anniversary dates for each quarter but just having a problem getting the oldest anniversary date to show. The report runs quarterly with all agents that reach a milestone year in the next quarter. I hope this makes sense and any help is appreciated.
Here are the formulas:
@Age_Next_Quarter
Truncate(DateDiff('d', {AGMRV_HIER.CONTRACT_DTE}, {@Next_Quarter_End_Date})/365.25, 0)
@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)
@Notable _Anniversary
If Remainder({@Age_Next_Quarter},5) = 0
Then 'Y'
Else 'N'
I have @Notetable_Anniversary suppressed in the group footer. The report is grouped by agent number.
So, they would want to see the anniversary date of 12/27/90 in 2 more years because the report only shows agents in milestones of 5-year increments. It’s showing the other 2 milestone years but it’s not his original ann date and they don’t want to see him for 2 more years, so he shouldn’t be on the report at all until he reaches his 35 year with the oldest date. The report is working fine getting anniversary dates for each quarter but just having a problem getting the oldest anniversary date to show. The report runs quarterly with all agents that reach a milestone year in the next quarter. I hope this makes sense and any help is appreciated.
Here are the formulas:
@Age_Next_Quarter
Truncate(DateDiff('d', {AGMRV_HIER.CONTRACT_DTE}, {@Next_Quarter_End_Date})/365.25, 0)
@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)
@Notable _Anniversary
If Remainder({@Age_Next_Quarter},5) = 0
Then 'Y'
Else 'N'
I have @Notetable_Anniversary suppressed in the group footer. The report is grouped by agent number.
Talk To Other Members
RE: Need help getting oldest anniversary date to show
If it was you could create a group selection formula:
CODE
{ann date}=minimum({ann date},{agt name})Macola Software Veteran and SAP Business One Consultant on Training Wheels
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Need help getting oldest anniversary date to show
RE: Need help getting oldest anniversary date to show
The only downside is that it will change the sort order to Agent Name rather than Agent Number.
Without understanding the report structure (ie, the groups that exist and where the various fields and formulas are placed within those groups), it is impossible to advise on other changes that may be required to formulas etc, but I am confident it is workable.
In need, if you are prepared to post the actual report file with saved data, I'd be happy to take a look if you need further guidance.
Cheers
Pete.