Print Page | Close Window

28 Day Prior Date Selection and YTD

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=13803
Printed Date: 27 Apr 2024 at 8:35am


Topic: 28 Day Prior Date Selection and YTD
Posted By: joe_crystal
Subject: 28 Day Prior Date Selection and YTD
Date Posted: 19 Jul 2011 at 9:43am
Hi,

New to CR am hoping you can help! I need to select data for two time periods. A start date and end date are being inputted via parameters.

Calc1:
The first date selection needs to be 28 days prior to the end date of the previous year.

Calc 2:
The second date selection needs to be a YTD calculate for the previous year (so 1/1/prev year to the end date of the previous year).

For example, if the user enters 6/10/2011 00:00:00 as the start date and 6/16/2011 00:00:00 as the end date, for calc 1 we need 28 days prior to and including 6/16/2010. For calc 2 we need YTD from 1/1/2010 thru 6/16/2010.

I'm guessing using DateAdd is the way to go but am having trouble with the syntax and am also not sure if the time becomes an issue when doing the comparisons.

Thanks so much in advance!





Replies:
Posted By: DBlank
Date Posted: 19 Jul 2011 at 10:26am
I am assuming you are saying you have a date field in a table that you want to filter on and have it included in either of those 2 date ranges
 
table.date in dateadd('d',-28,{?param1}) to {?param1}
or
table.date in dateserial(year({?param2})-1,1,1) to dateadd('yyyy',-1,{?param2})

Posted By: joe_crystal
Date Posted: 19 Jul 2011 at 2:04pm
I think this partially gets me there...but I also need to subtract 1 from the year for the first calculation. So, I need to subtract 28 from the days and 1 from the year. Can you please confirm what that syntax would be?

Thanks!

Posted By: DBlank
Date Posted: 23 Jul 2011 at 2:21am
Sorry Joe,
Been out for a few days.
try this
 
table.date in dateserial(year({?param1})-1,month({?param1}),day({?param1})-28) to dateadd('yyyyy',-1,{?param1})
or
table.date in dateserial(year({?param2})-1,1,1) to dateadd('yyyy',-1,{?param2})


Print Page | Close Window