Contact US

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

Need help getting oldest anniversary date to show

Need help getting oldest anniversary date to show

Need help getting oldest anniversary date to show

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:

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

If Month(Today) in [1 to 3]
Then Date(Year(Today), 6, 30)
If Month(Today) in [4 to 6]
Then Date(Year(Today), 9, 30)
If Month(Today) in [7 to 9]
Then Date(Year(Today), 12, 31)
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.

RE: Need help getting oldest anniversary date to show

Why isn't it grouped by Agent Name?

If it was you could create a group selection formula:


{ann date}=minimum({ann date},{agt name}) 

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:


RE: Need help getting oldest anniversary date to show

It needs to be grouped by agent number or tax id number. I have tried the option you suggested with tax id number but it didn't work.

RE: Need help getting oldest anniversary date to show

I would retain the Agent Number grouping and add an Agent Name grouping one level higher (so if Agent Number is Grp1, add a new Group for Agent Name and drag it up so it becomes Grp 1 and the Agent Number group is 'demoted' to Grp2), suppressing the new Grp1 Header & Footer. That way you could use Dgillzs Group Selection Formula.

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.


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