Help with an MDX Measures Formula

[Former Member]

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

[kirill_gromyko · Accepted solution]

The following measure is covering subtracting year.JAN from year.Q1, year.total for any year.

MEMBER [MEASURES].[ZQTD] AS

'IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL",

SUM(PERIODSTODATE([%TIME%].[LEVEL00],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD},-[MEASURES].[/CPMB/SDATA]),

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

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD},-[MEASURES].[/CPMB/SDATA]),

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

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTSIBLING},-[MEASURES].[/CPMB/SDATA]),

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL",

SUM(PERIODSTODATE([%TIME%].[LEVEL00],[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD.FIRSTCHILD},[MEASURES].[/CPMB/SDATA]),

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

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD},[MEASURES].[/CPMB/SDATA]),

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

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTSIBLING},[MEASURES].[/CPMB/SDATA]),

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",

-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

[kirill_gromyko · Accepted solution]

oh... noticed the JAN was subtracting from itself. It is fixed with

MEMBER [MEASURES].[ZQTD] AS

'IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL",

SUM(PERIODSTODATE([%TIME%].[LEVEL00],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD},-[MEASURES].[/CPMB/SDATA]),

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

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD},-[MEASURES].[/CPMB/SDATA]),

IIF([%TIME%].CURRENTMEMBER.PARENT.PROPERTIES("2/CPMB/PERIOD")="Q1" AND NOT [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN",

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTSIBLING},-[MEASURES].[/CPMB/SDATA]),

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL",

SUM(PERIODSTODATE([%TIME%].[LEVEL00],[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD.FIRSTCHILD},[MEASURES].[/CPMB/SDATA]),

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

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTCHILD},[MEASURES].[/CPMB/SDATA]),

IIF([%TIME%].CURRENTMEMBER.PARENT.PROPERTIES("2/CPMB/PERIOD")="Q1" AND NOT [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN",

SUM(PERIODSTODATE([%TIME%].[LEVEL01],[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])-SUM({[%TIME%].CURRENTMEMBER.FIRSTSIBLING},[MEASURES].[/CPMB/SDATA]),

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",

-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

[Former Member · Accepted solution]

Thank you SO much for your help. The code you gave me worked. I had to tweak it a little but overall it gave me what I needed. I have it to the customer for them to review it before moving it to production.

[kirill_gromyko]

MEMBER [MEASURES].[ZQTD] AS

'IIF([%P_ACCT%].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%].[2008.JAN]),-[MEASURES].[/CPMB/SDATA]),

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

IIF([%P_ACCT%].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%].[2008.JAN]),[MEASURES].[/CPMB/SDATA]),

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",

-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

[kirill_gromyko]

The below formula takes into account YEAR.Q1 as well

MEMBER [MEASURES].[ZQTD] AS

'IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",

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

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

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",

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

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

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

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02])),

IIF([%P_ACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",

-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3