Need some help with MDX formula

[Former Member]

Hello, I am not finding any good documentation on MDX formulas, there is a specific one that I need some help with....could someone please elaborate what exactly does this formula mean? thanks.

(MEASURES.[SIGNEDDATA],

CLOSINGPERIOD([%TIMEDIM%].

[%TIMEBASELEVEL%]))

[Former Member · Accepted solution]

Hi Zalak,

You will get list of all the MDX functions with example in the below link:

http://msdn.microsoft.com/en-us/library/ms145970.aspx

Hope this helps.

[Former Member · Accepted solution]

Thanks Nilanjan. I have goen through that and also list of mdx functions on SAP Help site. But there are a couple of ones that are not listed, I have mentioned a script below. e.g. What exactly does measures stand for in this script? what does YTD() stand for? I think may be ytd stands for YTD value?

Please advise. thansk.

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",SUM(YTD(),-MEASURES.[SIGNEDDATA]),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",SUM(YTD(),MEASURES.[SIGNEDDATA]),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",IIF([DATASRC].CURRENTMEMBER.PROPERTIES("ID")="CTA", SUM(YTD(),MEASURES.[SIGNEDDATA]),(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",IIF([DATASRC].CURRENTMEMBER.PROPERTIES("ID")="CTA", SUM(YTD(),-MEASURES.[SIGNEDDATA]),-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))),-MEASURES.[SIGNEDDATA]))))

[Former Member · Accepted solution]

One more thing Nilanjan, If I change the formulas for the measures in the measures table, will those changes be effective right away or do I need to do anything?

Thanks.

[Former Member · Accepted solution]

Hi,

You need to process the dimension, before the updated measure can take effect.

Hope this helps.

[Former Member · Accepted solution]

I dont even see measures dimension in the dimension list and also in the process dimension pop up screen.

[Former Member · Accepted solution]

Hi,

I am sorry about my previous post.

Its true that you wont be able to see the measure dimension in the measure list. You, actually, need to process the application (not the dimension). Processing the application will make sure that the modified measure formula takes its effect.

Hope this helps.

[Former Member · Accepted solution]

Thanks Nilanjan, I have been looking everywhere for little more explanation on MDX functions Could you please tell me the difference between these two formulas? Because when I run a report that has formula 1 for specific datasrc, and formula 2 for rest of the datasrcs, there is no diff in the way values are being displayed.

Formula 1: MEASURES.[SIGNEDDATA]

Formula 2: (MEASURES.[SIGNEDDATA],

CLOSINGPERIOD([%TIMEDIM%].

[%TIMEBASELEVEL%]))

Thanks for your help.

[krishna_priya1 · Accepted solution]

If new MEASURE gets added to Measure's table , in BPC Client , etools->client Options-> Refresh Dimensions should get the new measure into the CurrentView .IF not try etools->client Options-> Clear Local application information .

Formula 2 :

(MEASURES.SIGNEDDATA,

CLOSINGPERIOD(%TIMEDIM%.

%TIMEBASELEVEL%))

This formula is used to retrieve AST & LEQ account values.

If current time member is at monthlevel ,ie ClosingPeriod of 2011.FEB is the current member it self ,and would retrieve 2011.FEB value.

If Current time member is at quarter level then ClosingPeriod of 2011.Q1 is 2011.Mar ,and would retrieve 2011.MAR value.

If Current time member is at year level then ClosingPeriod of 2011.TOTAL is 2011.DEC ,and would retrieve 2011.DEC value.

Formula 1 :

MEASURES.SIGNEDDATA

Irrespective of the level of the Time , MEASURES.SIGNEDDATA would retrieve current Time member value.This formula is used to retrieve INC & EXP account values

if current time member is at month level,2011.FEB , 2011.FEB value is retrieved.

if current time member is at quarter level,2011.Q1 , 2011.Q1(JANFEBMAR) value is retrieved.

if current time member is at year level,2011.TOTAL , 2011.TOTAL(JAN,FEB,....,DEC) value is retrieved.

Hope this helps.