Print Page | Close Window

YTD formula

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=21376
Printed Date: 05 May 2024 at 6:34pm


Topic: YTD formula
Posted By: CrystalGirl
Subject: YTD formula
Date Posted: 13 Mar 2015 at 2:23pm
Hello,

I am new to Crystal reports anyone can help me to do a formula to calculate the YTD?

I am creating a yearly report called "Accounting".

Here are the data from column name:
{Year}, {Month},{EndingMonth},{Credit} and {Debit}

Year Month EndingMonth Credit Debit
2015 1     1/31/2015    100     300
2015 2     2/28/2015    100     300
2015 3     3/31/2015    100     300
2015 4     4/30/2015    100     300
2015 5     5/31/2015    100     300
2015 6     6/30/2015    100     300
2015 7     7/31/2015    100     300
2015 8     8/30/2015    100     300
2015 9     09/31/2015   100     300
2015 10    10/30/2015   100     300
2015 11    11/31/2015   100     300
2015 12    12/30/2015   100     300

There are 3 parameters I am using on the report.
1. @Year (from the field {Year}
2. @Month (from the field {Month}
3. @EndingMonth (from the field {EndingMonth}

my MTD formula is = round({Credit} - {Debit})

What I am trying to achieve is:
If I select:
@Year = 2015, @Month = 01 and @EndingMonth = 01/31/2015 then
the MTD is 200 and YTD is 200

and if I select @Year = 2015, @Month = 02 and @EndingMonth = 02/28/2015
then the MTD is 200 and YTD is 400

and so on...until the end of the year of 2015.

Here are the data I want on the report:

Year Month EndingMonth Credit Debit MTD YTD
2015 1     1/31/2015      100   300   200    200
2015 2     2/28/2015      100   300   200    400
2015 3     3/31/2015      100   300   200    600
2015 4     4/30/2015      100   300   200    800
2015 5     5/31/2015      100   300   200    1000
2015 6     6/30/2015      100   300   200    1200
2015 7     7/31/2015      100   300   200    1400
2015 8     8/30/2015      100   300   200    1600
2015 9     09/31/2015     100   300   200    1800
2015 10    10/30/2015     100   300   200    2000
2015 11    11/31/2015     100   300   200    2200
2015 12    12/30/2015     100   300   200    2400

Anyone can help with the YTD formula?

Thanks guys!



Replies:
Posted By: paulhoving
Date Posted: 15 Mar 2015 at 11:49pm
You could use a running total.

Insert a running total field from the field explorer.
Choose MTD as the 'field to summerize'
Choose Sum as Type of summery.

Insert the running total field in the details section of your report.

Paul


Posted By: CrystalGirl
Date Posted: 17 Mar 2015 at 2:08pm
Hi Paulhoving,

Thank you for the response and your time. The problem is, I have to run the report every month. So for example, if I run January report, I have YTD for January, but if I run February report, I only get the February running YTD. So, how do I include the YTD for January on the February report?

So basically when I run the report, I only run for one month. I want to be able to make my YTD dynamic depending what month I choose. If I choose, March, then I should have YTD from January, February and March added but right know, my YTD amount is based on the period I choose. If I choose March, my current YTD is the YTD amount of March only.

for example:
Report 1: run on January
Year Month EndingMonth   Credit Debit MTD    YTD
2015 1     1/31/2015      100   300   200    200

Report 2: run on March
Year Month EndingMonth   Credit Debit MTD    YTD
2015 3     3/31/2015      100   300   200    600

I really appreciated your time and help Paulhoving. Do you have any idea how to solve this?


Posted By: paulhoving
Date Posted: 18 Mar 2015 at 3:05am
I am not sure exactly what you are trying to accomplish.

So this is your table?
Year Month EndingMonth Credit Debit
2015 1     1/31/2015    100     300
2015 2     2/28/2015    100     300
2015 3     3/31/2015    100     300
2015 4     4/30/2015    100     300
2015 5     5/31/2015    100     300
2015 6     6/30/2015    100     300
2015 7     7/31/2015    100     300
2015 8     8/30/2015    100     300
2015 9     09/31/2015   100     300
2015 10    10/30/2015   100     300
2015 11    11/31/2015   100     300
2015 12    12/30/2015   100     300


Then you want to run the report with selection january, and a month later feb...

If you want to calculate the YTD in febraury, you also need the data of january in your selection, so you might want to use suppress.

Do you know how to suppress?

In the section expert, details, click on the formula button from 'suppress (No Drill-down)'.
Enter the formula

Courier[Month} <> {?Month}


Where {?Month} is the parameter field in which you choose the month.

Is this helpfull?


Posted By: CrystalGirl
Date Posted: 21 Mar 2015 at 6:51am
Hi Paulhoving, I tired and it does not show total YTD for January and February when I run February report. Yes, I know how to do the suppress...


Posted By: jkwrpc
Date Posted: 21 Mar 2015 at 7:19am
I just quickly skimmed this, so I could be missing something. Have you added groups to your report so it groups by month. Its bit more involved but you should be able to group your data by month, suppress the details and have the report footer shou your year to date.

You can create parameters to show or hide the details or even just a particular month.

I dont know if you are using them but CR has a YearToDate function and a similar MonthToDate function built in. Using those if you are not may help with your formula creation.



Posted By: CrystalGirl
Date Posted: 21 Mar 2015 at 7:29am
Hi jkwrpc,

Thanks for the responded. I have group my data but not by month...how do create a parameter to show or hide the detail and just to show specific month?

I don't use the YTD function in CR, I am trying to find it...

Thanks again



Print Page | Close Window