Re Automated dates in Crystal
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=15938
Printed Date: 06 May 2024 at 8:52pm
Topic: Re Automated dates in Crystal
Posted By: raruna19
Subject: Re Automated dates in Crystal
Date Posted: 21 Mar 2012 at 4:26am
|
Hi All,
We have a requirement for reports showing MTD and YTD dates
Supposing the current year is 2012,
MTD is for current month and YTD is Current Month if Month=1 i.e Jan
YTD for Feb is Jan 1st through Feb 28th or 29th based on Leap Year
for previous Year, we get
MTD for Dec will be Dec 1st 2011 through Dec 31 2011
YTD will be Jan 1st 2011 through Dec 31st 2011.
Can anybody suggest a formula for this?
Thanks in advance,
ARK
|
Replies:
Posted By: rkrowland
Date Posted: 21 Mar 2012 at 4:48am
|
MTDStartDate
date(year(currentdate),month(currentdate),1)
YTDStartDate
date(year(currentdate),1,1)
EndDate
dateadd(d,-1,dateadd(m,1,{@MTDStartDate}))
Regards,
Ryan.
|
Posted By: DBlank
Date Posted: 21 Mar 2012 at 5:11am
|
you can also use built in functions of MonthToDate and YearToDate
examples:
{table.date} in MonthToDate
{table.date} in YearToDate |
Posted By: rkrowland
Date Posted: 21 Mar 2012 at 5:19am
|
Originally posted by DBlank
you can also use built in functions of MonthToDate and YearToDate
examples:
{table.date} in MonthToDate
{table.date} in YearToDate Handy, never knew about those! MonthToDate may come in use at somepoint, however our financial year doesn't tie in with the calendar year.
I'm assuming the OP wants to be able to change the "currentdate" by use of parameters aswell, as he posted something about prior years - the formulas above should easily be adjusted to incorporate parameters to select which month/year he wants to view.
Regards,
Ryan.
|
Posted By: DBlank
Date Posted: 21 Mar 2012 at 5:38am
|
Thare are also the LastYearMTD and LastYearYTD
i assumed the example for last year was just an example if "today was last december".
However rereading the post the desired "MTD" and "YTD" are not standard definitions and in this case always run through the end of the month rather than today.
monthtodate and yeartodate only include values up to today and not through the end of the current month.
I also was assuming this wasjust for record selection and not to dispaly the actual dates.
assuming this is for record selection I would amend my approach to deal with including the full month.
currentdate could be replaced with a runtime param
//MTD
year(currentdate)=year(field) and month(currentdate)=month(field)
//YTD
year(currentdate)=year(field) |
Posted By: raruna19
Date Posted: 22 Mar 2012 at 1:03pm
|
Thanks for your responses Ryan. Would like to know if there is any chance of automating these dates in Crystal i.e for MTD showing Current month and YTD showing previous month dates till current month end to avoid changing the formulas each month.
Let me know if this is possible in Crystal.
|
Posted By: rkrowland
Date Posted: 23 Mar 2012 at 1:21am
|
Originally posted by raruna19
Thanks for your responses Ryan. Would like to know if there is any chance of automating these dates in Crystal i.e for MTD showing Current month and YTD showing previous month dates till current month end to avoid changing the formulas each month.
Let me know if this is possible in Crystal. That's what the formulas do. You won't ever need to change them provided you only want to see current MTD and current YTD.
|
Posted By: raruna19
Date Posted: 05 Apr 2012 at 1:12am
|
QuicK Question Ryan: End Date here can be used for every month? or is it for a specific month i.e
where MTD = YTD? eg January
|
Posted By: rkrowland
Date Posted: 05 Apr 2012 at 3:01am
|
Hi,
The enddate is relative to the end of the currentmonth. IE we'll use today (05-Apr-2012) as an example the 3 formulas will return the following;
MTDStartdate = 01-Apr-2012
YTDStartdate = 01-Jan-2012
EndDate = 30-Apr-2012
Is this correct or do you need a separate enddate formula that would return 31-Dec-2012?
If so that formula would be;
|
Posted By: raruna19
Date Posted: 05 Apr 2012 at 3:37am
|
Yes that is what I am looking for. Will try that n my report.
Have a nice day
 |
Posted By: raruna19
Date Posted: 05 Apr 2012 at 4:23am
|
Ryan,
Will these formulas work for every month? Without changing in the report?
|
Posted By: rkrowland
Date Posted: 05 Apr 2012 at 4:32am
|
Yes as currentdate always equals todays date. So on May the 10th the formulas will equal;
MTDStart = 01-May-2012
YTDStart = 01-Jan-2012
EndDate = 31-May-2012
Regards,
Ryan.
|