Using Member Formulas, how do I pull a different time other than current member?

[kyle_tetschlag]

I am trying to create Member Formulas for Days Payable Outstanding (DPO) and Days Sales Outstanding. The monthly calculation I can figure out by the following:

[ACCOUNT].[ZBSAP] / [ACCOUNT].[ZFACOSSTD]*30

[ZBSAREXCL] / [ZFANETSALES] / 30

The full year is where I need help. To fill in the the below equation, I can't figure out how to get the sum of month's EB AP. We use a period 13 so I can't take the balance as of 2017.TOTAL. I have to either use YTD for 2017.12 or 2017.TOTAL - 2017.13.

((Sum of the month's Ending Balance Accounts Payable - Trade) / 12) / ([ACCOUNT].[ZFACOSSTD] / 360)

I tried the equation below but didn't work:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",((-([TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZBSAP]-[TIME].CURRENTMEMBER.PROPERTIES("YEAR")&".13",[ACCOUNT].[ZBSAP])/12)/([TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZFACOSSTD]/360)),(-[TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZBSAP]/[TIME].CURRENTMEMBER.ID,[ACCOUNT].[ZFACOSSTD]*30))

[former_member186338 · Accepted solution]

Just tested on my system:

PL250 with member formula:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",(CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[PL110]),NULL)

Report result:

Tested on BPC without HANA ("LEVEL02").

Please show administration screenshot of your TIME dimension! Something is wrong here...

[kyle_tetschlag · Accepted solution]

I built off of your formula and worked through some other parts of the equation and got it to work like it is suppose to. Thank you for your help on this. Not sure what was throwing it off before but possibly missing parenthesis or did put in the IIF statement.

[former_member186338 · Accepted solution]

"missing parenthesis" - for sure!

[former_member186338]

Use correct syntax first of all!

Not:

CLOSINGPERIOD( [TIME].CURRENTMEMBER,[TIME].[LEVEL02])

But:

CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER)

First argument is level!!!

The level syntax depends on your system - on HANA it's different ("LEVEL 02" - with space)!

And use simple test to check very simple formula!

[former_member186338]

P.S. Read note: https://launchpad.support.sap.com/#/notes/0001958603

[kyle_tetschlag]

I put in a simple equation CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[ZFANETINCOME] and I am still getting an error when refreshing worksheet (see below). Is there something I need to update in our system? or a note to apply? When I try to save this member formula in development, I cannot process the dimension either.

One or more errors occurred.

The execution of report Default Report failed. Please contact your administrator.

Bad request.

Server message:

code: UJO_READ_EXCEPTION_018

severity: error

description: MDX statement error: An exception with the type CX_SY_CONVERSION_NO_NUMBER occurred, but was neither handled locally, nor declared in a RAISING

log id: uDjL2X4a7jYQcuGdb0tf1G

DATAVALUE:An exception with the type CX_SY_CONVERSION_NO_NUMBER occurred, but was neither handled locally, nor declared in a RAISING

MDX:

V1:An exception with the type CX_SY_CONVERSION_NO_NUM

V2:BER occurred, but was neither handled locally, nor

V3: declared in a RAISING

V4:

----------------- Framework message: The remote server returned an error: (400) Bad Request.

The remote server returned an error: (400) Bad Request.

[former_member186338]

P.S. It's a bad idea to reply after a long period...

[former_member186338]

Use ClosingPeriod MDX function to get the value of the 13 month!

[kyle_tetschlag]

Not sure where I am off. The below gives me an error message

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",((-[ZBSAP]-ClosingPeriod([TIME].CURRENTMEMBER),[ZBSAP])/12)/([ZFACOSSTD]/360)),(-[ZBSAP])/12/([ZFACOSSTD]*30)

[former_member186338]

"Not sure where I am off."

Have you read MDX function specification on Microsoft site? Must read if you want to play with MDX!

https://docs.microsoft.com/en-us/sql/mdx/closingperiod-mdx?view=sql-analysis-services-2017

[former_member186338]

Hint: Level is not optional if you specify member!

[kyle_tetschlag]

I tried taking out the member but that didn't work.

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",((-[ZBSAP]-CLOSINGPERIOD(),[ZBSAP])/12)/([ZFACOSSTD]/360)),(-[ZBSAP])/12/([ZFACOSSTD]*30)

Then I tried finding the level to put in (LEVEL1, LEVEL2, LEVEL3, LEVEL01,LEVEL02,LEVEL03) and none worked.

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",((-[ZBSAP]-CLOSINGPERIOD(

[TIME].CURRENTMEMBER,[TIME].[LEVEL02]),[ZBSAP])/12)/([ZFACOSSTD]/360)),(-[ZBSAP])/12/([ZFACOSSTD]*30)

[former_member186338]

For sure it will not work due to unsupported:

[TIME].CURRENTMEMBER.PROPERTIES("YEAR")&".13"

[kyle_tetschlag]

I have 2 properties for 2017.TOTAL: YEAR = 2017 and MONTHNUM = 12. Is there a way to combine these in a formula to pull YTD for 2017.12? or is there a better way to go about this?

[former_member186338]

You cant simply combine strings in MDX. You will have to use strtomember function, but this function is currently not working with concatenation in SAP MDX 😞

Look on my second answer!