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=21389
Printed Date: 09 May 2024 at 1:33pm


Topic: YTD formula
Posted By: CrystalGirl
Subject: YTD formula
Date Posted: 21 Mar 2015 at 8:28am
Hi all,

I am still working on my YTD calculation so I am wondering if anyone can help me to solve this?

Here is the actual report that I am working on.

January Report:
Item Description        MTD   YTD
Travelling Cost        $6800 $6800
Medical cost           $ 400 $ 400
Car Maintenance cost   $ 750 $ 750

February Report:
Item Description        MTD   YTD
Travelling Cost        $1000 $1000
Medical cost           $ 500 $ 500
Car Maintenance cost   $ 200 $ 200

March Report:
Item Description        MTD   YTD
Travelling Cost        $ 500 $ 500
Medical cost           $ 200 $ 200
Car Maintenance cost   $ 100 $ 100

The formula for my MTD is: round({Debit} - {Credit})

Here are the data/fields I am using:
Item Description is from column: {ItemDescription}
Debit : {Debit}
Credit: {Credit}
FiscalYear: {FiscalYear}
FiscalPeriod: {FiscalPeriod}

The parameters I have on the report:
{?FiscalYear} in {FiscalYear} (value:01,02,03,04,05,06,07,08,09,10,11,12)
{? FiscalPEriod} in {FiscalYear} (value: 2015)

So, what I am trying to achieve is to have the right amount of YTD on February, March and so on report...currently my YTD is the MTD amount on each month.

So the report should looks like this:
January Report:
Item Description        MTD   YTD
Travelling Cost        $6800 $6800
Medical cost           $ 400 $ 400
Car Maintenance cost   $ 750 $ 750

February Report:
Item Description        MTD   YTD
Travelling Cost        $1000 $7800
Medical cost           $ 500 $ 900
Car Maintenance cost   $ 200 $ 950

March Report:
Item Description        MTD   YTD
Travelling Cost        $ 500 $8300
Medical cost           $ 200 $1100
Car Maintenance cost   $ 100 $1050

thought about create a YTD formula and assigned my YTD for a specific MTD for is not working...for example:
MTD01: round(({Debit} in ({FiscalPeriod}="01") - ({Credit}) in {FiscalPeriod} = "01"))
MTD02: round(({Debit} in ({FiscalPeriod}="02") - ({Credit}) in {FiscalPeriod} = "02"))
MTD03 for march and so on until MTD12 for December


YTD=
if {FiscalPeriod} = 01 then MTD01
else
if {FiscalPeriod} = 02 then MTD01 + MTD02
else
if {FiscalPeriod} = 03 then MTD01 + MTD02 + MTD03
and so on until {FiscalPeriod} = 12

Anyone can help?



Replies:
Posted By: DBlank
Date Posted: 23 Mar 2015 at 7:27am
I think you are complicating this too much and you are trying to get sub -type summaries inside each month/year which becomes more difficult as you would need unique formulas per type.
 
Are you married to this outcome design or could you use grouping at the Item instead like this?
Travelling Cost
Month               MTD       YTD 
Jan                  $6800    $6800 
Feb                $ 1000     $7800 
Mar                 $ 500      $8300
 
If you absolutely need it the way you orginally posted it are there only the 3 Item types (travel, medical, maintence) or are there more and can more be added?


Print Page | Close Window