BusinessObjects Board

where clause

building reports webintelligence
#1

Hi there -

I have a request to take 8 weeks of forecast and pull out each one separately to use in a calculation. I am trying to use a where clause in my variable with no luck.

Here is the details:

Week measure from DB returns current week
Query that returns week and forecast for the current week plus 8 weeks.

I need to create the following variables:
current week forecast - Inventory
(current week forecast + current week + 1 forecast) - Inventory
(current week forecast + current week + 1 forecast + current week + 2 forecast) - Inventory
AND SO ON FOR NEXT 8 weeks

I created a variable for each of the 8 weeks to pull out the forecast seperatly but it’s not working like I expect it too. My formula includes:

wk1forecast = forecast WHERE (weekinyear = currentweek)

I am getting a multivalue error when I try this.

How can I pull out each forecast into a separate variable?

kmt1234 (BOB member since 2013-02-11)

#2

Do you have a Forecast Date object for each of the forecast weeks?

#3

hi Mark -

No I don’t have a forecast date available. The only thing I have is the week number. So for this example I have this:

week in year:
30
31
32
33
34
35
36
37
38

I also have a object with just the current week in it

WeeKYear
30

I was hoping to use that as the starting point and add one to it. But the where clause in the variable is not pulling out the one week.

I can’t use a cross tab because I’m already using the cross tab for the forecast amounts. I need to show this calculation prior to the cross tab body.

kmt1234 (BOB member since 2013-02-11)

#4

Question - do you need to work with year overlaps, i.e. reporting against weeks 50, 51, 52, 1, 2, 3, 4, 5?

That would change the solution slightly

#5

Yes I will need to. I have the year seperated from the week in a object as well as combined like this

502013
512013
522013
012014
022014

kmt1234 (BOB member since 2013-02-11)

#6

Try something along the lines of:

wk1forecast = SUM (IF Wweekinyear = currentweek THEN forecast ELSE 0)

Andreas :de: (BOB member since 2002-06-20)

#7

Thanks for the idea but it isn’t working. I am getting a Multivalue result. I added a ForEach to the statement to help restrict the summing but that isn’t working either.

kmt1234 (BOB member since 2013-02-11)

#8

How is the universe measure object “forecast” defined in the universe? With a proper SQL aggregation as well as a projection? See also here: diff between Select SUM (Table. Field) & Select Table. F

Andreas :de: (BOB member since 2002-06-20)