Print Page | Close Window

Dynamic YTD and MTD

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=14645
Printed Date: 29 Apr 2024 at 10:11pm


Topic: Dynamic YTD and MTD
Posted By: achandana01
Subject: Dynamic YTD and MTD
Date Posted: 11 Oct 2011 at 12:19pm
Hi,
 
I am working on a report that need YTD & MTD but I am not sure how to create YTD & MTD dynamically.
 
I have something like this
 
MTD   YTD
Amount  Qty   Amount  Qty
 
 
but I have no clue how to do it without using any parameters to ask for the date range.
 
 
Any help will be greatly appreciated.


-------------
chand


Replies:
Posted By: achandana01
Date Posted: 11 Oct 2011 at 1:55pm
and I for got to add I also need to put custom date range as well

-------------
chand

Posted By: Robotacha
Date Posted: 11 Oct 2011 at 11:53pm
there are crystal functions in the Formula workshop called:
 
monthtodate
 
yeartodate
 
these should help with what you require.
 
(there are lots of date functions that should help with your date range)


-------------
Regards,

Michael Jones

Posted By: Robotacha
Date Posted: 11 Oct 2011 at 11:54pm
an example of monthtodate (from Crystal Help)

If {orders.ORDER DATE} in MonthToDate Then {orders.ORDER AMOUNT}



-------------
Regards,

Michael Jones

Posted By: achandana01
Date Posted: 12 Oct 2011 at 10:51am
Thanks for your reply Jones,that will help me to schedule a report dynamically however it will fail if I ran the report in January.
 
Yeartodate will give the day of the curreny year,but when a report was ran in January 2012  it should populate the date for MTD - December and YTD - (Jan - Dec)
 
so I am looking for something that will work for all cases.Thank you Jones its a great help.


-------------
chand

Posted By: DBlank
Date Posted: 12 Oct 2011 at 11:25am
one way...
if month(currentdate)>1 and {orders.ORDER DATE} in yeartodate Then {orders.ORDER AMOUNT}
else if month(currentdate)=1 and year({orders.ORDER DATE})=dateadd('yyyy',-1,currentdate) then {orders.ORDER AMOUNT}

Posted By: MWToine
Date Posted: 13 Oct 2011 at 9:41am
Hello,
 
I think you should take into consideration WHEN you plan to run the report.  If you're running the report at the close of the the period (current month -1) then your formula must reflect that.
 
One way is the include the entire period because there shouldn't be any data saved in advance (current date/time +).
 
Your MTD formula may look like this:
 
If Month(CurrentDateTime) >1 Then //Current Date of Feb and beyond
     If Month({Date/Time Field}) = Month(currentdatetime)-1
         and Year([Date/Time Field]) = Year(Currentdatetime) Then
                {Your Value Field}
Else if Month(CurrentDateTime) = 1 Then
    If Month({Date/Time Field}) = Month(currentdatetime)-1 //Or hardcode 12 
         and Year([Date/Time Field]) = Year(Currentdatetime) -1 Then
                {Your Value Field}
Else
               00.00;
 
Your YTD formula is much easier:
If Year([Date/Time Field}) = Year(CurrentDateTime) Then
         {Your Value Field}
Else
         0.00;
 
 
I hope this answers your question.  Good luck!


Print Page | Close Window