using _add_days in prompt expression

[manias]

Hi!
I have member like this:
[cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))# - [cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].[20141215]

#nper# working by calendar prompt and return beginning of period.
I need change the prompt to return day-1.

I try to use the _add_days in the following example, but faced with the challenge of converting - "Can not convert value string '2014-12-15' to data type timestamp with time zone."
#sb(substitute ('-';'';substitute ('-';'';_add_days(prompt('[nper]';'date');-1))))#

how i can change my prompt expression for using _add_days function, or there are other ways to return -1 day?

Egor.

[MFGF]

Hi!
I have member like this:
[cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))# - [cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].[20141215]

#nper# working by calendar prompt and return beginning of period.
I need change the prompt to return day-1.

I try to use the _add_days in the following example, but faced with the challenge of converting - "Can not convert value string '2014-12-15' to data type timestamp with time zone."
#sb(substitute ('-';'';substitute ('-';'';_add_days(prompt('[nper]';'date');-1))))#

how i can change my prompt expression for using _add_days function, or there are other ways to return -1 day?

Egor.

How about using the prevMember() function around your initial expression - ie

prevMember([cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))#)

Would this work for you?

MF.

[manias]

MF, thanks for reply!

When I use the prevMember function, I get an error:
XSB-ERR-0016 Execution of MDX failed:
XSB-ERR-0028 BAPI Error while executing Function BAPI_MDDATASET_SELECT_DATA:
RFC Error: For characteristic nper, enter value in a permitted format.

As source i have sap bw.

Egor.

[MFGF]

MF, thanks for reply!

When I use the prevMember function, I get an error:
XSB-ERR-0016 Execution of MDX failed:
XSB-ERR-0028 BAPI Error while executing Function BAPI_MDDATASET_SELECT_DATA:
RFC Error: For characteristic nper, enter value in a permitted format.

As source i have sap bw.

Egor.

Hmmmm. That's worrying. What happens if you use the lag() function, with 1 as the number of members to lag - ie

lag([cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))#,1)

MF.

[manias]

I get same error. I think will continue to be an error similar to the return of the previous member.
Trying to figure out how to substitute the value -1

Egor.