YTD formula help
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=21395
Printed Date: 09 May 2024 at 3:11pm
Topic: YTD formula help
Posted By: CrystalGirl
Subject: YTD formula help
Date Posted: 23 Mar 2015 at 7:01am
Hi all,
Can anyone fixed my YTD formula?
YTD:
If OnFirstRecord then
round({Dataset_.Credit}-{Dataset_.Debit}) else
Previous ({@MTD Revenue}) + round({Dataset_.Credit}-{Dataset_.Debit})
I am trying to have the YTD on each Description A, B and C, but the only thing that is correct is the YTD for 02 on Description A.
My MTD revenue formula is: {@MTD Revenue}:
round({Dataset_.Credit}-{Dataset_.Debit})
| Description |
Debit |
Credit |
Fiscal Period |
Fiscal Year |
MTD Revenue |
YTD Revenue |
|
|
|
|
|
|
|
| A |
$2,750 |
$8,550 |
01 |
2015 |
$5,800.00 |
$5,800.00 |
| |
|
|
|
|
|
|
| A |
$1,350 |
$6,500 |
02 |
2015 |
$5,150.00 |
$10,950.00 |
| |
|
|
|
|
|
|
| A |
$2,250 |
$4,500 |
03 |
2015 |
$2,250.00 |
$7,400.00 |
|
|
|
|
|
|
|
| B |
$1,550 |
$5,500 |
01 |
2015 |
$3,950.00 |
$6,200.00 |
| |
|
|
|
|
|
|
| B |
$1,450 |
$7,500 |
02 |
2015 |
$6,050.00 |
$10,000.00 |
| |
|
|
|
|
|
|
| B |
$4,500 |
$5,550 |
03 |
2015 |
$1,050.00 |
$7,100.00 |
|
|
|
|
|
|
|
| C |
$1,250 |
$4,600 |
01 |
2015 |
$3,350.00 |
$4,400.00 |
| |
|
|
|
|
|
|
| C |
$1,500 |
$5,500 |
02 |
2015 |
$4,000.00 |
$7,350.00 |
| |
|
|
|
|
|
|
| C |
$3,250 |
$6,000 |
03 |
2015 |
$2,750.00 |
$6,750.00 |
What I want for the YTD on each description is like below:
Desciption YTD
A $5,800
A $10,950
A $13,200
B $6,200
B $12,250
B $13,300
C $3,350
C $7,350
C $10,100
Anyone can fix my YTD formula?
Thanks all
|
Replies:
Posted By: DBlank
Date Posted: 23 Mar 2015 at 7:40am
|
I answered your other post because it has differnt sorting.
The above is easily handled by a Running Total (or shared variable formula.
First create one formula called 'MTD' (or whatever you want).
table.Credit-table.debit
place this in the MTD colum to get your sum for each month/description
ow in th field explorer go to Running total ans select New
name = YTD_per_Description (or whatever you want)
field to mailto:summarize=@MTD - summarize=@MTD formula
type = sum
evaluate = on each row
reset= on change of field -select description
place in detail section next to MTD
|
Posted By: CrystalGirl
Date Posted: 23 Mar 2015 at 10:56am
Hi DBlank,
The problem with the running total, when I select the parameter of FiscalPeriod (ex: 01,02 or 03), it returned the YTD amount as the MTD of that period. So, it doesn't sum the previous MTD if I select for example February or March.
|
Posted By: DBlank
Date Posted: 23 Mar 2015 at 11:10am
|
what is your select statement?
|
Posted By: DBlank
Date Posted: 23 Mar 2015 at 11:16am
|
also are you maintaining the sorting in your above examples:
description
Fiscal Year
Fiscal Period
or are you moving things around like in your other post?
|
Posted By: CrystalGirl
Date Posted: 23 Mar 2015 at 11:28am
Hi Dblank,
My select statement is:
SELECT Dataset.Description,Dataset.Debit, Dataset.Credit, Dataset.FicalPEriod, Dataset.FiscalYear
FROM Dataset
Basically the sorting what I have on this sample but I will not display them all periods at once. I have to report it as monthly.
The running total for YTD is working if I display all the records in one report but once I assign the period as a parameter, the running total YTD is the MTD on that current month.
All period on one report, running total returning correct amount on each description on per period:
Description Debit Credit Fiscal Period Fiscal Year MTD Revenue YTD
A $2,750 $8,550 01 2015 $5,800.00 $5,800.00
A $1,350 $6,500 02 2015 $5,150.00 $10,950.00
A $2,250 $4,500 03 2015 $2,250.00 $13,200.00
B $1,550 $5,500 01 2015 $3,950.00 $3,950.00
B $1,450 $7,500 02 2015 $6,050.00 $10,000.00
B $4,500 $5,550 03 2015 $1,050.00 $11,050.00
C $1,250 $4,600 01 2015 $3,350.00 $3,350.00
C $1,500 $5,500 02 2015 $4,000.00 $7,350.00
C $3,250 $6,000 03 2015 $2,750.00 $10,100.00
So, if I set parameter fiscalperiod to 01, my YTD is the MTD of 01 (this is correct) but when I set the parameter fiscalperiod to 02, my YTD is the MTD of 02 (wrong), as well as the 03 and I believe the same for 04, 05 and so on.
This is the report on 01:
Description Debit Credit Fiscal Period Fiscal Year MTD Revenue YTD
A $2,750 $8,550 01 2015 $5,800.00 $5,800.00
B $1,550 $5,500 01 2015 $3,950.00 $3,950.00
C $1,250 $4,600 01 2015 $3,350.00 $3,350.00
and this is the report on 02:
Description Debit Credit Fiscal Period Fiscal Year MTD Revenue YTD
A $1,350 $6,500 02 2015 $5,150.00 $5,150.00
B $1,450 $7,500 02 2015 $6,050.00 $6,050.00
C $1,500 $5,500 02 2015 $4,000.00 $4,000.00
Do you know how to fix this issue if I just want to display the data on monthly basis but still have the YTD in correct amount?
|
Posted By: CrystalGirl
Date Posted: 23 Mar 2015 at 2:40pm
Dblank,
I figured it out the issue but I used the idea of Running Total from you. I then set on the details suppress if <> to my period parameter or otherwise the YTD amount will be as my MTD, by suppressing period <> @period I then can see the correct amount of my YTD.
Again..thank you for your help...you are genius!
|
|