this year and last year as a variable

[system]

This might seem simple but we have had trouble with this for a long time.

We’d like to show this year’s figures vs. last year’s figures and not have to hard code a year in a variable.

example:

we’d like

= Where (= 2007)

to be

= Where (= some variable)

so we don’t have to change the code every year. Any ideas?

The error we bet is incorrect data type when we try

= Where (= (Year(CurrentDate())))

Thanks in advance!

---

RBogartz (BOB member since 2003-06-11)

[system]

You can create the following variables in your report, make sure your object is a Number:

CurrentYear=Sum(If <Net Fiscal Year> = Year(CurrentDate()) Then <Net Pairs>)

LastYear=Sum(If <Net Fiscal Year> = Year(CurrentDate())-1 Then <Net Pairs>)

This should give you the same result as your current formula.

Hope that helps

---

BO_Chief (BOB member since 2004-06-06)

[system]

That seemed close but when I tried it I got an #IERR in my cells. I tried modifying it and got the #computation error too.

Any suggestions? Your suggestions are very much appreciated.

Thanks,

Russ

---

RBogartz (BOB member since 2003-06-11)

[system]

I believe some of the problem may come from the fact that our fiscal year is not the same as a calendar year. Our fiscal year starts in Feb.

Russ

---

RBogartz (BOB member since 2003-06-11)

[system]

@RBogartz

That seemed close but when I tried it I got an #IERR in my cells. I tried modifying it and got the #computation error too.

Any suggestions? Your suggestions are very much appreciated.

Thanks,

Russ

Make sure the objects ‘Net Fiscal Year’ and ‘Net Pairs’ are added to the report table.

Use Format Table–>Pivot --> Add .

If you do not want to display then you can HIDE it.

---

BO_Chief (BOB member since 2004-06-06)

[system]

Okay…we are REALLY close!!

Is there a way to truly hide the year though? I have pairs by gender and what’s happening is I’m getting 4 rows instead of 2

2006 Men’s Pairs
2006 Ladies Pairs
2007 Men’s Pairs
2007 Ladies Pairs

you follow?

Thanks so much for your help!

---

RBogartz (BOB member since 2003-06-11)

[system]

If you use an If statement then the variable you reference has to be in the block. If you use Sum() Where(…) then you do not have to include the year as part of the block.

There is a FAQ entry on how to use a variable in the Where(…) portion of that formula.

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

But if you use the where statement in the formula, wouldn’t that require a constant? I’m trying to get out of hard coding…

ex. No more (where = 2007)

I will study up on it though…

Thanks for your help!

---

RBogartz (BOB member since 2003-06-11)

[system]

@RBogartz

But if you use the where statement in the formula, wouldn’t that require a constant? I’m trying to get out of hard coding…

@Dave Rathbun

There is a FAQ entry on how to use a variable in the Where(…) portion of that formula.

Emphasis added

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Oops…sorry I missed that.

Thanks for clearing that up. I will try to find it again.

Russ

---

RBogartz (BOB member since 2003-06-11)

[system]

I linked it in my post, in case that wasn’t obvious.

FAQ: Reporter

Why do I get a Syntax Error (DMB0007) when I try to Sum() Where ( > 2000)? The syntax of WHERE Clause is: measure Where (dimension = (number or character string or date, dimension = …)) This means that the WHERE Clause in Business Objects Reporter only allows the “=” operator, and the right side of the equation has to be a constant as well (and not another report variable etc.). For example: Sum(<Sales>) Where (<Year> = 2000) will work Work around for original problem: Create ano…

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Okay,

Sorry if I’ve missed your point but to me it looks like I’d still have to put in a hard coded year somewhere.

A colleague of mine got this to display the way I need by providing a combination of separate data providers and pre-defined filters. I think this could potentially be much more work but I may have to go with that if I can’t figure out what you’re trying to point out to me here.

Thanks!

Russ

---

RBogartz (BOB member since 2003-06-11)

[system]

Russ, in your original post you had this formula:

=<Net Pairs> Where (<Net Fiscal Year>= (Year(CurrentDate()))) 

That does not require hard-coding anything, but it doesn’t work in it’s current form because of the limits on the Where() option.

What you need are two steps:

Current Year Flag = if (<Net Fiscal Year> = Year(CurrentDate())) THen 1 else 0

Then

=Sum(<Net Pairs>) Where (<Current Year Flag>=1)

Make sense?

Either that, or I have read your question wrong, and I apologize for leading you down the wrong path…

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

Unfortunately our fiscal year is not simply based on the year derived from the current date. Our fiscal year starts in February.

Thanks for your help though.

Russ

---

RBogartz (BOB member since 2003-06-11)

[system]

You must have the value somewhere, right? Is there a prompt involved?

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

This thing all started as an effort to get rid of as many prompts as possible…so no.

---

RBogartz (BOB member since 2003-06-11)

[system]

What version of BO are you using?

---

Dave Rathbun (BOB member since 2002-06-06)

[system]

6.5

---

RBogartz (BOB member since 2003-06-11)