BusinessObjects Board

formula problem

building reports desktopintelligence
#1

Hi

I have 2 columns like this:

Year YTDLeads
2005 7,222

2006 9,307

2007 11,634

Now, I want to find out division for YTD Leads for 2007 and 2006.
I have used this syntax = Where ( =2007) / Where ( = 2006) . It works good.

How to make this generic, I mean by using current year or previous year, as next year the formula will change to Leads ytd(2008) / leads ytd(2007)
I am a little lost how to do this.

any help will be greatly appreciated.

Thanks in advance

neha008 (BOB member since 2007-07-09)

#2

forgot to add some things.

The data is coming from the excel sheet. current year is available in the excel sheet that has 2007 value.
BO version is 6.1a.

thanks

neha008 (BOB member since 2007-07-09)

#3

Try reading this FAQ and see if that helps…

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

#4

How many years of information you are going to display in the report…

If you have variable # of years then it is difficult. You have to create local variables for your years…like first extract the year and subtract 1 or 2 or 3 to get previous years.

But you need variables… locally declared in your report.

BO_Chief :us: (BOB member since 2004-06-06)

#5

Hi

Thanks for all your replies.

Current Year is coming from the Excel. ( current year = 2007)

I made a local variable.

Previous Year (dimension)
Code: =( -1).

For LeadsYtd ( measure) for Current Year,
Code: = Where ( = 2007). This one is giving me the value 11,634 as expected.

But For LeadsYtd ( measure) for Previous Year,
Code: = Where (= 2006), it still gives me 11,634 … I should be getting 9,307.

Any hints for this…

Thanks in Advance.

neha008 (BOB member since 2007-07-09)

#6

Some changes here. I cant hard code the value for current year i.e. 2007.

So , I will have to do it dynamically.

How do I find the ‘LeadCountYTD’ for current year and previous year??

Any hints will be really appreciated.

Thanks
Neha

neha008 (BOB member since 2007-07-09)

#7

Thanks a lot for all your hints and clues.

Issue got resolved.

Thanks Again!!!

neha008 (BOB member since 2007-07-09)

#8

The solution came from Dave’s reply.

Thanks Dave!!

neha008 (BOB member since 2007-07-09)