Previous Period MDX for universe

[Former Member]

Hello Friends Please help me with MDX Syntax, I want to derive the previous period revenue so I want to write the MDX in where clause in universe..

My current period works fine as below syntax , please edit to make it prior period = current user prompt entered period - 1

<FILTER KEY="[ZFISCPER].[LEVEL01]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT NAME="@Prompt('Select Fiscal Month','A','Period\Fiscal Month', mono,constrained) "/>

</CONDITION></FILTER>

If I write -1 as below it throw syntax error in webi please correct it

<FILTER KEY="[ZFISCPER].[LEVEL01]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT NAME="@Prompt('Select Fiscal Month','A','Period\Fiscal Month', mono,constrained) - 1 "/>

</CONDITION></FILTER>

Thanks

Soniya

[Former Member · Accepted solution]

Hi,

Check whether class name\objectname is correct or not. And if your formula doesn'twork then also try this formula for the variable where you define prompt = to_date("@Prompt('Select Fiscal Month','A','Period\Fiscal Month', mono,constrained) " ,-1)

Cheers,

Suresh Aluri

[Former Member · Accepted solution]

Hi,

This is MDX and not SQL, so To_date is not a supported MDX function.

In Soniya's case, the previous period MDX is based on Calendare Month whereas the solution provided by Rohit is based on Calendar Year.

It works for Calendar Year because it is numeric. To obtain an equivalent result for Calendar Month we need to convert it into date and then use the calculation.

To do that we need the CDate function that is, unfortunately, not supported by SAP whereas it is supported by MSAS.

Depending of the Calendar Month striucture we might use LEFT, MID and RIGHT operators to tranform it into numeric values like this: YYYYMM.

Then use IIF statement to compute the previous period:

<ul><li> -1 for Month when Month >= 2

<li> -1 for Year and Month = 12 when Month = 1

</ul>

Regards,

Didier

[Former Member · Accepted solution]

Hello All. I agree that I am using Calendar year/month, can u please correct code if u think left or mid is going to work. Please help to fix my MDX code since I am not good to fix this MDX Code using Left etc .

Thanks

soniya

[Former Member · Accepted solution]

Hello Friends Please help me correct the MDX I want to write the Code based on fiscper , but i have a pseudo code which does not work also with Fiscal year. please help me with syntax

<FILTER KEY="[0FISCYEAR].[LEVEL01]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT NAME="@Prompt('Select Fiscal Month','A','Period\Fiscal Month', mono,constrained) "/>

IIF(CONSTANT NAME = "2006", <CONSTANT NAME="@Prompt('Select Fiscal year','A','Fiscal Year\L01 Fiscal Year',mono,constrained)-2"/>,<CONSTANT NAME= "2006"/>

</CONDITION></FILTER>

[IngoH · Accepted solution]

Hi,

I assume you are using a BW query as a data source ? Why would you not use an SAP EXIT variable in the BW query to solve the problem ?

ingo

[Former Member · Accepted solution]

Hello All. Its my pleasure to hear from all of you experts. BW Query is not an option for us since business does not want the query approach at all due to limitation on creating multiple structures in query. hence i am using the Multiprovider so I just need the MDX Code to create a variable for previous period for user prompted fiscper.

please help if any one implemented the MDX for this or Script in universe

Thanks

Kind Regards

Soniya

[Former Member · Accepted solution]

Hi,

If your goal is to retrieve key figures values for previous period, you just have to write the follwing MDX expression.

Solution 1 for the current key figure if only one in the query:

[0CALMONTH].Currentmember.PREVMEMBER

Solution 2 for a given key figure (in that case Open order quantity in base unit)

([0CALMONTH].Currentmember.PREVMEMBER, [Measures].[0D_OORQTYBM])

Regards,

Didier

[IngoH · Accepted solution]

Hi,

I assume you do realize that when accessing the cube directly

- you will lose restricted keyfigures

- you will lose calculated keyfigures

- you will lose navigational attributes

- you will lose authorization variable (keep in mind BI authorizations are not working like a filter !).

ingo

[Former Member · Accepted solution]

Hello Experts. Your help and guidance is highly appreciated. However my request is to base these previous periods for order qty based on user response Calmonth and also I need trended like user response current period -2, user response current period -3, --- user response current period -6

Solution 2 for a given key figure (in that case Open order quantity in base unit)

([0CALMONTH].Currentmember.PREVMEMBER, [Measures].[0D_OORQTYBM])

I can open a new post if you consider this more appropriate. Thanks for helping me to atleast get the previous values with above which works but i need to base it on user response and not hard coded.

Thanks

Soniya

[Former Member · Accepted solution]

Hi,

If you want to use dynamic periods using prompt with positive or negative values, the syntax is:

<EXPRESSION> ([0CALMONTH].Currentmember.Lag(@Prompt('Enter period','N',,mono,free)), [Measures].[0D_OORQTYBM])</EXPRESSION>

You will have the n previous or the n next periods depending on the user response.

Regards,

Didier

[Former Member]

Hi,

I tried with the following code and its working:

<FILTER KEY="[0CALYEAR].[LEVEL01]">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT NAME="@Prompt('Select Fiscal year','A','Calendar Year\L01 Calendar Year', mono,constrained)-1"/>
</CONDITION></FILTER>

Please check your object and class names and also the Filter Key (it should be the generated definition of the object)...

Regards,

Rohit