| Author |
Message |
raruna19
Newbie
Joined: 13 Dec 2011
Online Status: Offline
Posts: 8
|
   Topic: Re Automated dates in Crystal
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
|
 IP Logged |
|
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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)
|
|
IP Logged |
|
raruna19
Newbie
Joined: 13 Dec 2011
Online Status: Offline
Posts: 8
|
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.
Edited by raruna19 - 22 Mar 2012 at 1:04pm
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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.
|
|
IP Logged |
|
raruna19
Newbie
Joined: 13 Dec 2011
Online Status: Offline
Posts: 8
|
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
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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;
date(year(currentdate),12,31)
Regards,
Ryan.
|
|
IP Logged |
|
raruna19
Newbie
Joined: 13 Dec 2011
Online Status: Offline
Posts: 8
|
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  |
|
IP Logged |
|
|
|
