Skip to Content
0
Jul 19, 2012 at 05:25 PM

Help with an MDX Measures Formula

44 Views

Hello Everyone,

I have a measures formula that I have updated that I need some assistance with. It is basically checking to see if the member selected is an INC account type and if it's parent's period is Q1 then it should subtract the amount for January 2012 from the total. I need this measure as the client wants to sum up QTD values and exclude January 2012. When I validate it I get a syntax error

of MDX Statement Error: "Invalid MDX Command with ,"

If I remove the IIF([%TIME%].CURRENTMEMBER.PARENT.PROPERTIES("2/CPMB/PERIOD")="Q1" OR I REMOVE THE IIF([%ACCOUNT%].CURRENT,MEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC". The logic validates fine. I have validated each piece of the logic individually and it works. It's only when I put it all together that I get this error. I believe it's a syntax error but I can't seem to find out why it doesn't work.

MEMBER [MEASURES].[CUSTQTD] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

IIF([%TIME%].CURRENTMEMBER.PARENT.PROPERTIES("2/CPMB/PERIOD")="Q1",SUM(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].[2012.JAN]),-[MEASURES].[/CPMB/SDATA]),SUM(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])),

IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP", IIF([%TIME%].CURRENTMEMBER.PARENT.PROPERTIES("2/CPMB/PERIOD")="Q1",

SUM(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].[2012.JAN]),[MEASURES].[/CPMB/SDATA]),SUM(PERIODSTODATE([%TIME%].[LEVEL01], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),

IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

Any Help at all would be appreciated.

Thanks,

Eleasha