Fiscal Year to Date Help!!

[bbrendan]

Hi,

Im hoping someone can help me out here with what would seem an easy problem. But im just being a bit stupid! :¬)

The end user would enter in the date param
(date from) 01/07/2003 to (Date to) 31/10/2003
this would return the following information

I-----Period Jul to Oct 2003-----I
Customer Net Value Net Volume Return Val
ABC LTD 1000,00 2000 500


Then next to this (to the right)have the figures for the same period but the previos year

I-----Period Jul to Oct 2002-----I
Customer Net Value Net Volume Return Val
ABC LTD 1000,00 2000 500

I can get the current period information no problem, but how would I build the formula to get the information for the last year

thanks
brendan

 

[dgillz]

Take a look at the dateadd() function, and use it somewhat similar to the following:

If {Datefield} in Dateadd("y",-1,{?StartDate}) to Dateadd("y",-1,{?EndDate}) then {AmountField} else 0

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

http://www.trianglepartners.com

 

[synapsevampire]

The dateadd will likely eliminate the SQL pass thorugh if you're using this to limit rows returned by the database in the record selection formula, try just building a date as in:

(
{table.date} >= {?StartDate}
and
{table.date} <= {?EndDate}
)
or
(
{table.date} >= date(year({?StartDate})-1,month({?StartDate}),day({?StartDate})
and
{table.date} <= date(year({?EndDate})-1,month({?EndDate}),day({?EndDate})
)

the latter could also be used as the basis for a Running Total or formula for summaries, although in a formula the dateadd will be fine providing it's NOT in the record selection formula.

Something like that anyway...

-k

 

[bbrendan]

hi to both,

many thanks for your suggestions to solving this delima!. Im still having some diffculties though!
I dont get it!!!

I have a {?date} paramater which for example I enter in 12/12/2003.

In the current Net Val Field I get the correct results, but in the last year to date I get 0.00

My formula for LYTD is:

if {SAPTSD_HIST.File_Date} = Dateadd(&quot;d&quot;,-2,{?Date})
then
{SAPTSD_HIST.NET-VAL} else 0

Ive done this to just test the data from the date entered -(minus) 2 days just to see how it works, but nada!!

Would you have any ideas where im going wrong?

thanks again for your help

brendan

 

[bbrendan]

hi,

I think I cracked it, I had to make forumlas for the Net Value Column. Obvious, but sometimes you have to take a step back.

thanks for your help

brendan