Help with YTD formula 1

[kagee]

Hi,

I am kinder stuck with some crystal YTD formula, and now seeking help.

Crystal version: 8.5
Database and connectivity: SQL Server
Sample data:

Am using a MS SQL server view to select the following data
Date, Account No, Account Desc, Amounts

Ps: Currently am using a record selection formula, 2 parameters and 2 formulas. These are working only for data from Jan2005- date (gives all values for 2004 as ZERO)

{?sdate} = parameter name of start date
{?edate} = parameter name of end date

My record selection formulas are...
(
(
{table.date} in [dateserial(year({?sdate}),
month({?sdate}),
1) to
dateserial(Year({?edate)),
Month({?edate})+1,
1-1)
) or
(
{table.date} in [dateserial(year({?sdate})-1,
month({?sdate}),
1) to
dateserial(Year({?edate))-1,
Month({?edate})+1,
1-1)
)
)

.. and my formulas are:

YTD AMOUNT
if {table.date} in YearToDate and {table.date} <Minimum(MonthToDate) then {table.amount} else 0

LYTD AMOUNT
if {table.date} in LastYearYTD and {table.date} <Minimum(LastYearMTD) then {table.amount}else 0

Ps: i have placed these formulas on my report canvas

Expected output:
My main problem is that my formulas return values from Jan to date, yet i want values from Sept 2004 to date.

Please assist

 

[dgillz]

I would change your record selection formula to this:

{table.date} in date(year({?sdate),month({?sdate}),1) to {?edate}

The above would give you 2 years of info that you need to get YTD and LYTD.

The change your YTD formulas as follows:

@YTD
if {table.date} in {?Sdate} to {?Edate} then {table.amount} else 0

@LYTD
if {table.date) in dateadd("y",-1,{?Sdate}) to dateadd("y",-1,{?Edate}) then {table.amount} else 0



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz