Skip to Content
avatar image
Former Member

Custom measure BPC 10 NW

Hi All,

I am implemented an YearToGO custom measure, I need to set a value equal to last month of an year (e.g. year 2017 I need 2017 december)

I am trying to use TAIL function but when I add the custom measure in the report the data area became blank.

I have tested the code on T-code MDXTEST and it is working fine.

Following the code (only the part with tail function):

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

TAIL([%TIME%].[LEVEL01],1)

Thanks

Nilla

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    Jan 17, 2017 at 03:04 PM

    Try this one:

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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 17, 2017 at 12:17 PM

    Please explain what do YOU mean by YearToGO measure with some example screenshot!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 17, 2017 at 01:01 PM

    ok sorry, please find below the full code

    IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC", SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], CLOSINGPERIOD([%TIME_PL%].[LEVEL00])),-[MEASURES].[/CPMB/SDATA])-SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], [%TIME_PL%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]), IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP", SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], CLOSINGPERIOD([%TIME_PL%].[LEVEL00])),[MEASURES].[/CPMB/SDATA])-SUM(PERIODSTODATE([%TIME_PL%].[LEVEL00], [%TIME_PL%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]), IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",

    TAIL([/CPMB/MOD19WI].[LEVEL01],1),

    IIF([%FUNCT_ACCOUNT_PL%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ", NULL, -[MEASURES].[/CPMB/SDATA])))) ';SOLVE_ORDER=3

    Following the expected result.

    The formula for EXP is fine

    But For AST we need to have the last value of the year. The AST account is used to store the FTE.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 17, 2017 at 01:35 PM

    But what do you mean by:

    TAIL([/CPMB/MOD19WI].[LEVEL01],1)

    Has to be:

    TAIL([%TIME_PL%].[LEVEL01],1)

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 17, 2017 at 01:47 PM

    yes sorry you are write, I put

    TAIL([%TIME_PL%].[LEVEL01],1)

    it was a wrong copy and paste.

    but with this instruction when I add the custom formula the report became empty.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 17, 2017 at 02:18 PM

    Try the following for AST:

    '([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME_PL%].[LEVEL00]))';SOLVE_ORDER=3

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 17, 2017 at 02:48 PM

    Thanks, I used the code but the result is not what expected.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 18, 2017 at 09:04 AM

    Hi Vadim,

    Thanks a lot! it works fine!

    Just another question, I have to create a new formula every time that I insert a change in the code. It seems that it is not possible to change the code after the first save.

    Do you know if there is a way to avoid this?

    Add comment
    10|10000 characters needed characters exceeded