YTD

[hoppeli]

Hi.

My record selection formula is as follows:
{table.date} in {?sdate} to {?edate}
or {table.date} in {?comp_sdate} to {?comp_edate}

Formula for sales is simply:
if {table.date} in {?sdate} to {?edate}
then {sales.amount}
else 0

(And similar to comparison formula for comparison date range.)

By this I list customers sales when user gives dates (e.g. years 2004 compared to 2003). It works fine with the given formula, which then is subtotaled and grandtotaled.
Now my boss wants me to make sales YTD and sales LYTD, but all attempts to make it with this record selection formula has not given me right results. So, what is wrong? should I rewrite the record selection formula?

I recall I would have seen in 'Select Expert'-box YTD-possibility.

Thanks in advance!

Hoppeli

 

[lbass]

Go to report->edit selection formula->record and enter:

{table.date} in YearToDate or
{table.date} in LastYearYTD

Then you can still use your parameters in the formulas for date ranges within those periods. You would need to go to the set default values screen within the parameter set up and check "Range limited field" for each parameter so that the user could not select dates outside of the range YTD or last year YTD. If you want to allow ranges outside of those, then the record selection formula would have to be adapted.

-LB

 

[hoppeli]

Hi Lbass and thanks for your quick response.

It did not work. I am quite suspicious of my ytd-formula, which is by the book, i.e. like Crystal guide tells.

What might be our advise of the right formula?

Tks

Hoppeli

 

[lbass]

Please copy the formula you used into this thread.
Saying something didn't work isn't informative--it is better to supply sample results and explain how they differ from what you would like.

-LB

 

[hoppeli]

Sorry LB, I am a little bit in middle of a chaos.

Here are those:
//@YTD
{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

BTW: partially it fetches data, but according my testing, it does not work...

Tks

Heikki

 

[lbass]

You still didn't say whether the range sdate to edate could fall outside of the year to date formulas, but assuming that they cannot, then or your record selection formula, use:

{table.date} in YearToDate or
{table.date} in LastYearYTD

For your {@YTD}, use:

if {table.date} in YearToDate then {table.amount}

For your {@LastYTD}, use:

if {table.date} in LastYearYTD then {table.amount}

If you want some subset of dates within the YTD period, and assuming you limited the dates to a specific range when setting up the parameters, use:

if {table.date} in {?sdate} to {?edate} then {table.amount}

For the same subset of dates within the previous year use:

if {table.date} in dateserial(year({?sdate})-1,month({?sdate}), day({?sdate})) to dateserial(year({?edate})-1,month({?edate}), day({?edate})) then {table.amount}

-LB