Joined: 22 Feb 2015
Online Status: Offline
Posts: 36
Topic: YTD formula help 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
Joined: 22 Feb 2015
Online Status: Offline
Posts: 36
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.
Joined: 22 Feb 2015
Online Status: Offline
Posts: 36
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?
Joined: 22 Feb 2015
Online Status: Offline
Posts: 36
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.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
