asked on

YearToDate function for past years?

How do I right a formula to retrieve YTD data for past years, I know the function for last year is LastYearYTD but what about prior years like 2004 and 2003, etc. I assume this has to be fairly simple and straight forward but I can't seem to figure it out.

Example:
If {trade.settle_i} in LastYearYTD-1 Then
{trade.prin_i}
Else
0

Thanks for your help!

Brian

ASKER CERTIFIED SOLUTION

frodoman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

frodoman

Or a more generic formula that compares the month & day regardless of year:

if DATE(year(currentdate), month({trade.settle_i}), day({trade.settle_i}) < currentdate then ...

IO_Dork

ASKER

I am not sure if it makes a difference but I want to show YTD for the last five years or so, i.e. - this year 2006, along with 2005, 2004, 2003, 2002, 2001 and 2000. Does your first formula work in this case?

frodoman

No, you'd have to do +1 Or +2, etc. The second formula however will work for every year so if you want to see everything you can use this formula as is. If you want year 2000+ then do this (I assume you want this in the record selection formula?):

year({trade.settle_i}) >= 2000
and
DATE(year(currentdate), month({trade.settle_i}), day({trade.settle_i}) < currentdate

IO_Dork

ASKER

i used the first formula for all years and it worked great! Thanks!