Calculated Member - Current month

• ttdeveloper

  SSCrazy

  Points: 2370

  More actions

  June 30, 2015 at 12:30 pm

  #147342

  Good Day

  My situation is that the script below is for a calculated member but I need to add a condition into that only makes the else statement apply to the current month only.

  All other months (the Average LIS) needs to be used as in [Measures].[Disconnects] / [AVERAGELIS]

  CREATE MEMBER CURRENTCUBE.[Measures].CHURN

  AS CASE WHEN [Measures].[End LIS] = 0 THEN

  CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE

  ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END

  ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)

  END,

  NON_EMPTY_BEHAVIOR = { [Beginning LIS], [End LIS], [Disconnects] },

  VISIBLE = 1 ;

  I tried adding something like this but I knew while typing that it wasnt correct

  CASE WHEN [Measures].[End LIS] = 0

  AND [Dim Date].[FSCL_YR].&[" + Format(now(), "yyyy") + "]

  AND [Dim Date].[FSCL_YM].&[" + Format(now(), "m") + "]

  THEN

  CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE

  ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END

  ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)

  END

• Martin Schoombee

  SSCoach

  Points: 19116

  More actions

  July 2, 2015 at 12:27 pm

  #1810028

  I haven't tested this, but how about something like:

  ...and SetToStr([Dim Date].[FSCL_YR].[FSCL_YR].currentmember) = "[Dim Date].[FSCL_YR].&[" + Format(now(), "yyyy") + "]"

• ttdeveloper

  SSCrazy

  Points: 2370

  More actions

  July 6, 2015 at 8:13 am

  #1810451

  Thanks Martin.

  So I started tested with you code before I realised the .currentmember property only works at Hierarchy level.

  I modified my code to now look like the below.

  CASE WHEN [Measures].[End LIS] = 0

  AND SetToStr([Dim Date].[FSCL_YM].currentmember) = "[Dim Date].[FSCL_YM].[Month Nm].&[" + Format(now(), "yyyy") + "]&[" + Format(now(), "m") + "]"

  THEN

  CASE WHEN [Measures].[BEGIN_LIS] = 0 OR [Measures].[BEGIN_LIS] + [Measures].[BEGIN_LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE

  ROUND([Measures].[Disconnects]/(([Measures].[BEGIN_LIS] + [Measures].[BEGIN_LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END

  ELSE

  CASE WHEN [Measures].[BEGIN_LIS] = 0 THEN NULL ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)END

  END

  The code appears to work but is "jumping" over the case statement to moving to the ELSE portion as it is not evaluating the dim date value.

  I have attached a screenshot of what my date hierarchy looks like for [FSCL_YM]. An example value from the hierarchy for 2015 July is

  [Dim Date].[FSCL_YM].[Month Nm].&[2011]&[7]

• Martin Schoombee

  SSCoach

  Points: 19116

  More actions

  July 6, 2015 at 10:24 am

  #1810520

  Apologies for the shameless plug, but have a look at the following blog post which will describe your issue and how to test potential solutions:

  http://did-i-say-that.com/2015/04/16/mdx-testing-with-settostr-function/

• ttdeveloper

  SSCrazy

  Points: 2370

  More actions

  July 6, 2015 at 12:57 pm

  #1810550

  So i had this question posted on the MSDN Analysis Server Forum and got some help.

  All I was trying to do was evaluate the currentmember of the the Dim Date FCSL_YM hierarchy and check if that member matched the current year month.

  The syntax i was looking for was (in case someone ever needs help with this)

  [Dim Date].[FSCL_YM].CURRENTMEMBER.UNIQUE_NAME =

  "[Dim Date].[FSCL_YM].[Month Nm].&["+Format(now(), "yyyy")+"]&["+Format(now(), "%M")+"]"

  Got my calculation working now with the above embedded in a case statement.