Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: 28 Day Prior Date Selection and YTD Post Reply Post New Topic
Author Message
joe_crystal
Newbie
Newbie


Joined: 18 Jul 2011
Online Status: Offline
Posts: 3
Quote joe_crystal Replybullet Topic: 28 Day Prior Date Selection and YTD
    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!



IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
joe_crystal
Newbie
Newbie


Joined: 18 Jul 2011
Online Status: Offline
Posts: 3
Quote joe_crystal Replybullet 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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.063 seconds.