February 20, 2009 at 5:45 am
hi
i'm trying to create a calculated measure:-
([Measures].[Projected]/[Measures].[Principal Amt])-[BENCH].[RATE]
two things that's i'm struggling with:-
1) i want to say "if the projected measure is empty, return empty"
2) it doesn't work when there are measures...??
bit of an MDX noob...
February 20, 2009 at 6:20 am
As far as the IF:
[font="Courier New"]CASE
WHEN ISEMPTY([Measures].[Projected]) THEN NULL
ELSE ([Measures].[Projected]/[Measures].[Principal Amt])-[BENCH].[RATE]
END[/font]
or, you could use the NON-EMPTY behavior if [Measures].[Projected] is not a calculated measure. Using the NON-EMPTY behavior will help performance.
As far as it not working when the measures have values - what is [BENCH].[RATE]? It must be numeric. You have also made your query contextual (assuming [BENCH] is a dimension) so you probably have to have that dimension in your crosstab.
February 20, 2009 at 6:32 am
aha
many many thanks, that's solved the empty thing
have got
CASE
WHEN ISEMPTY([Measures].[Projected]) THEN NULL
ELSE (([Measures].[Projected]/[Measures].[Principal Amt])-StrToValue([BENCH].[RATE]))
but it's still returning #VALUE in Excel...
BENCH.RATE is a double, but it's a dimension
February 20, 2009 at 7:42 am
So [BENCH].[RATE] is a hierarchy. You cannot use a hierarchy in a mathematical calculation like this, you need to use a member. You may have to do some converting because I think member names will always be considered strings. You may be able to use: [BENCH].[RATE].CurrentMember.Properties("Key")
Again, this makes your calculation contextual because dimension hierarchy properties do not automatically aggregate. So, unless you have this dimension included in your crosstab, you will get an error.
February 20, 2009 at 8:02 am
wahey!
got it
even more thanks
i knew that damn thing would be fiddly and out my comfort zone!
😀
March 27, 2009 at 7:42 am
as a follow up question...
i have
CASE
WHEN ISEMPTY([Measures].[Projected]) THEN null
ELSE
CASE [Measures].[Projected]
WHEN 0 THEN 0
ELSE [Measures].[Projected]/[Measures].[Principal Amt]
END
-strToValue([BENCH].[BenchMarkRate].CurrentMember.Properties("Key"))
END
as my calculated [measures].[RateDiff]
work fine as long as the [BENCH].[BenchMarkRate].is being used on the rows, which is almost ok
problem is, at the aggregate levels, [BENCH].[BenchMarkRate].CurrentMember.Properties("Key") is EMPTY, so it just returns [Measures].[Projected]/[Measures].[Principal Amt]
what i want it to do is come up with a composite rate for all the members by doing sum([measures].[RateDiff]*[measures].[Principal Amt])/sum([measures].[Principal Amt])
and i'm struggling trying to do this in MDX...can anyone give me any pointers?
thanks in advance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply