Skip to Content
0

Custom measure BPC 10 NW

Jan 17, 2017 at 11:17 AM

84

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Best Answer
Vadim Kalinin Jan 17, 2017 at 03:04 PM
0

Try this one:

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


ytg.png (23.4 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Jan 17, 2017 at 12:17 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

P.S. Also it's a strange idea to show only part of custom measure...

0
p. porru Jan 17, 2017 at 01:01 PM
0

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.


ytg-example.png (14.0 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Jan 17, 2017 at 01:35 PM
0

But what do you mean by:

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

Has to be:

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

Share
10 |10000 characters needed characters left characters exceeded
p. porru Jan 17, 2017 at 01:47 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Jan 17, 2017 at 02:18 PM
0

Try the following for AST:

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

Share
10 |10000 characters needed characters left characters exceeded
p. porru Jan 17, 2017 at 02:48 PM
0

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


ytg-result.png (8.5 kB)
Share
10 |10000 characters needed characters left characters exceeded
p. porru Jan 18, 2017 at 09:04 AM
0

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?


epm-addin.png (28.9 kB)
sp.png (52.3 kB)
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Please Accept the correct answer and close the question!

801 SP09 - very old! Upgrade required...

EPM SP20 is also old!

0

Please Accept the correct answer and close the question!

BPC 801 SP09 is very old, upgrade required!

EPM SP20 is also old!

To edit measure use:

measure.png (29.6 kB)
0