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!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help getting oldest anniversary date to show

Status
Not open for further replies.
chish_nc

chish_nc

MIS
Aug 19, 2019
5
US
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:

dates_nr4ee8.jpg



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.
 
Sort by date Sort by votes
Why isn't it grouped by Agent Name?

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:

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.

Cheers
Pete.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

T
  • Locked
  • Question
Date comparison Formula
Replies
2
Views
2K
TPG_IT
T
D
  • Locked
  • Question
Comparing two Date Ranges
Replies
0
Views
451
doodledump
D
BradCustom
  • Locked
  • Question
Need help with setting a value to 0
Replies
0
Views
277
BradCustom
BradCustom
john432
  • Locked
  • Question
Crystal Report Help 1
Replies
2
Views
545
john432
john432
PJ222
  • Locked
  • Question
is date within any date range in a dynamic array of date ranges
Replies
1
Views
332
PJ222
PJ222

Part and Inventory Search

Sponsor

Back
Top