Print Page | Close Window

formula on date values in a group

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15391
Printed Date: 05 May 2024 at 6:14am


Topic: formula on date values in a group
Posted By: afrogers
Subject: formula on date values in a group
Date Posted: 17 Jan 2012 at 5:34am
I have a report which shows the start and end dates when users were members of an employment benefit, for some users there will be multiple entries, with the latest start date having no end date.  I need a formula which will flag those users who's latest start date is less than 52 weeks from when they previously left the scheme.  I think I'm going to have to use a sub-report but I'm a newbie to using arrays etc. Below is an example of a record in the report that meets the criteria:

User            Start Date    End Date

John Smith   22/05/11
                    22/09/10    20/04/11


Replies:
Posted By: lockwelle
Date Posted: 18 Jan 2012 at 3:20am
well if you group by user and order by start date, you could do a simple datediff, like:
 
if datediff("w", {table.startDate}, Previous{table.endDate}) < 52 then
  //flag the record
else
  //don't flag
 
HTH
ps, I never remember which order to put the dates in the datediff.  I tend to print the results and if it is negative, I reverse the date fields
 

Posted By: afrogers
Date Posted: 18 Jan 2012 at 4:30am
Many thanks, the previous function is the one I was after.


Print Page | Close Window