I want to check the number of the month and according to that divide by a certain number. The problem is that this code always defaults to the else
statement. Can anyone spot the issue? Thanks.
CREATE MEMBER CURRENTCUBE.[Measures].[SBBD]
AS case [Date].[Calendar Month Number Of Year]
when 2 then [Measures].[SB]/28
when 4 then [Measures].[SB]/30
when 6 then [Measures].[SB]/30
when 9 then [Measures].[SB]/30
when 11 then [Measures].[SB]/30
else [Measures].[SB]/31
end,
FORMAT_STRING = "Standard",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'LB';
Try just comparing to the member's value in the condition of the
CASE
statement. Currently you are comparing a member[Date].[Calendar Month Number Of Year]
which is not seen as a numeric expression inmdx
, and comparing it to a numeric expression (2, 4,...).Maybe the following works better:
I'd be more tempted to initially create a measure
[Measures].[NumOfMonth]
and then feed that into the case (chances are splitting in two like this will be quicker):OTHER SIDE OF THE COIN
Rather than worrying about values you could compare members. The condition of the case should use the
currentmember
function, and the numbers replaced by members: