Problem with formula returning correct year 1

[jeffm777]

I've got two formulas...

//@CurrentYear
if {Table.Date} >= date(year(currentdate),01,01)
then year({Table.Date})

//@OneYearAgo
if {Table.Date} >= date(year(currentdate)-1,01,01) and {Table.Date} <= date(year(currentdate)-1,12,31)
then year({Table.Date}

The first formula is returning the current year of 2010 but the second formula is returning 0 instead of 2009.

Can someone explain?

 

[fisheromacse]

what happens if you try this:

//{@Currentyear}
IF year({table.date})=year(currentdate) then year({table.date})

//{@OneYearAgo}
IF year({table.date})=year(currentdate)-1 then year({table.date})

 

[jeffm777]

Same thing.

 

[jeffm777]

Interesting...I put else 1 at the end of the {@OneYearAgo} formula and it returned 1.

 

[SkipVought]

You probably want...

//{@Currentyear}
IF year({table.date})[b][red]>[/red][/b]=year(currentdate) then year({table.date})

//{@OneYearAgo}
IF year({table.date})[b][red]<[/red][/b]=year(currentdate)-1 then year({table.date})

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lbass]

I think you should show us your actual formula, since the formula you are showing would return the correct year. Another thought is that maybe your selection criteria has not allowed for the previous year in your dataset? You have to include both years in your selection criteria.

-LB

 

[jeffm777]

My selection criteria is...

{Table.Date} >= Date (Year(currentdate)-3, 01, 01)

What other formula do you want to see? Basically, I have the above in my record selection and the year formulas in my report footer - that's it so far.

 

[fisheromacse]

If the 1st formula is true (thus showing the year 2010) the 2nd formula must be false. Do you have records where 2010 is not shown by the 1st formula and the 2nd formula is 0 while the year({table.date}) is for sure 2009?

Can you show a sample of your data & how it looks in the report currently & how you want it to appear?
thanks

this probably has no effect on your situation, but you can always add the below to the formulas to check for null/blank values in the data, i also added an else date() at the end:

//{@Currentyear}
IF (not(isnull({table.date})) and NOT(TRIM(totext({table.date}))="")) and year({table.date})=year(currentdate) then year({table.date})
else date(1900,01,01)

 

[lbass]

I mean--please copy and paste your formula into the thread instead of paraphrasing it.

-LB

 

[jeffm777]

if {InventoryAUT.DateEntered} >= date(year(currentdate)-1,01,01) and {InventoryAUT.DateEntered} <= date(year(currentdate)-1,12,31)
then year({InventoryAUT.DateEntered})

I need this to show 2009. I have data that spans 4 years: current year, 1 year ago, 2 years ago and 3 years ago. I need a similar formula to show the 4 years; 2010, 2009, 2008 and 2007 but have the years change in the future like when the report is run on 1/1/2011.

 

[jeffm777]

I will have a table and will need the years to show up in the table column headers like:

2007 2008 2009 2010
January
February
March
April
May
June
July
August
September
October
November
December

 

[jeffm777]

If I setup a subreport for each year and create shared variables which will be compiled on a calculation page, I think I can accomplish this...was just looking for an easier way.

 

[lbass]

The problem is not with the formula. The formula will return the desired result on only those rows which meet the criteria--otherwise the default value of 0 will be shown. If you are trying to create a label, then you should just use:

year(currentyear)-1 //last year, etc.

If you are trying to return data per year, and you would use similar conditional formulas, you would then insert sums on them at a group level and suppress the detail records (where you would see a set of some zeros and some with values).

-LB

 

[jeffm777]

I changed it to year(currentdate)-1 and it worked!!!! That's awesome dude, and so simple. It's like me to over complicate things .

Tks again!