Skip to Content

Help with an MDX Measures Formula

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Jul 20, 2012 at 11:21 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 20, 2012 at 09:48 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.