Year and Quarter

I have a interesting question.
I have a dimension variables “Year” and “Quarter” and a measure variable “Count”.
Year has value say 2003, 2004, 2005, 2006, 2007…
Quarter has 4 values -> 1, 2 ,3 , 4

Here will be the table like:

``````Year     Quarter       Count
-----     ---------       -------

2004        1                30
2004        2                11
2004        3                 2
2004        4                  38

2005        1                   22
2005         2                 59
2005        3                  42
2005         4                 51

2006        1                   22
2006         2                 59
2006        3                  42
2006         4                 51 .......``````

Lets say the table has only these data uptil 2006.
So I want the total data and the data from the last quarter (which is Y2006 Q4). I want to do it dynamically by creating a variable or some other way.

I created a variable(measure variable):
“Current Year” = Max(). I get 2006 here.

Now I am trying to create variable “Current Quarter” which will be like:
=Max() Where ( = Current Year)

But I am getting the incorrect data type syntax error.
If I write this, it works out fine:

=Max() Where ( = 2006). I get 4 here. But this method is not dynamic since current year can change

I could not find the problem between the two.
Can you tell how can I implement the design?

Thanks.

[Moderator Edit: Added code formatting - Dave Rathbun]

ujain82 (BOB member since 2007-06-19)

Hi, please see this FAQ entry. Thanks.

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

Its not solving my problem . It is addressing different problem.

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.).

But in my case right side needs to be a variable for my reports to work dynamic.
How can I do it? Can you read the problem description again.

Thanks.

ujain82 (BOB member since 2007-06-19)

It cannot be dynamic, that is the way it works…

ahm100 (BOB member since 2005-05-25)

But there should be a work around for the problem.

ujain82 (BOB member since 2007-06-19)

Please reread the FAQ. It shows you how to solve the problem.

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

I am not getting the solution from it.

It says: Sum() Where ( = 2000)
I need the same thing but instead of 2000 (constant) I want a variable which is not possible with Where clause.

This is my problem.
The FAQ is trying to solve Sum() Where ( > 2000) this problem which I don’t want.

Thanks.

ujain82 (BOB member since 2007-06-19)

The FAQ shows you how to use a variable on the right side of the expression. The example is simply that, an example.

Create a variable for Current Year.
Create a variable Current Year Flag

``=If (<Year> = <Current Year>) Then 1 else 0``

The final expression:

``=Sum(<Variable>) Where (<Current Year Flag> = 1)``

It’s all there in the FAQ; you just have to be able to extend the example to fit your situation.

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

I created a variable(measure):
“Current Year” = Max().

Now I could not create a Current Year Flag variable(measure).
If I do this:
= If(=) Then 1 Else 0 . It gives the computation error.

ujain82 (BOB member since 2007-06-19)

You need some way to determine the year without using an aggregate function. The requirement is that the value used in the WHERE clause has to be a dimension. When you use an aggregate, the value becomes a measure, and therefore is not valid.

For example, you can use a prompt and let the user tell you what the last year is.

But you cannot use any aggregate functions in this process.

You can potentially create a second data provider that brings back the maximum year only, using database functions. Then you can link the data providers, and generate the flag that way.

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

Hi Ujain82,

Were you able to fix this issue ?? I am having a similar issue here.

Thanks,
Neha

neha008 (BOB member since 2007-07-09)

I was able to resolve it.

Thanks

neha008 (BOB member since 2007-07-09)