using case when with cubes

[Benjamin80]

Hello,

I'm new to the forum and I was hoping somebody could help me with my problem.
Since a few months I'm using BI cognos, so let's say I'm a rookie on this matter.

I have made a crosstab based on a cube with following info:

	Year-1	Year	Difference
Month

I've dragged a measure in it and everything works fine.
Now to show you what my problem is I'll try to post an example of the table:

	Year-1	Year	Difference
Jan	10	5	-5
Feb	10

So the first line is ok, bit the problem is when there's no value available I don't get a subtraction result.

I've tried to use a case when like this

Code Select Expand

case [Y]
   WHEN null THEN -[Y-1]
   ELSE [Y]-[Y-1]
end

but I get following error:

Code Select Expand

In a Parent-Child hierarchy, a category can have only one parent.

Any ideas what's wrong with the method I use? Any advice or suggestions are welcome.
Thanks in advance for the help.

Benjamin

[Benjamin80]

Hello, I found a solution for my problem. I think the problem for me is still thinking in dimensions.

So I adapted the Y and Y-1 items like this:

Code Select Expand

if(tuple([MemberYear];currentmeasure) is null ) then (0) else (tuple([MemberYear];currentmeasure))

Perhaps this solution helps somebody else.

Best regards,

Benjamin