YTD with input month (MDX)

[Former Member]

hi all,

i'm working with a bw universe based.

I want to add a misure in my universe that calculates the ytd for a keyFigure (KF) using MDX code.

I found the YTD() function but I need to set the "end month" using something like a @prompt because the user want to input a month.

for example:

the user input 032008

YTD: 012008......032008

can someone help me?

thanks

[Former Member · Accepted solution]

Hi,

A YTD measure can be created as

  SUM(YTD([0CALMONTH].CurrentMember),[Measures].<object name>)) OR 
<EXPRESSION> SUM(YTD(@Select(Key Figures\Object name))) </EXPRESSION> 

Prompt can be created as:

 <FILTER KEY="Object Definition"><CONDITION OPERATORCONDITION="Equal"><CONSTANT TECH_NAME="@Prompt(' Calendar Year/Month','A','Class Name\LOV Object Name,mono,constrained/free/primary_key)"/></CONDITION></FILTER>

You can select last parameter in the prompt definition depending on the requirement

Regards,

Rohit

[Former Member · Accepted solution]

Hi,

If you want to prompt the user for selecting the YTD period to be used, you can write an expression like this;

<EXPRESSION>SUM(YTD(@Prompt('Select a month','N','Month class\Month key',mono,constrained)),@Select(Measure class\Measure parent))</EXPRESSION>

The Month Key object must be the technical_name of the object in order to generate an expression like this:

<EXPRESSION>SUM(YTD([0CALMONTH].[032008]),[Measures].[Oxxxxx])</EXPRESSION>

Regards,

Didier

[Former Member · Accepted solution]

Thank you very much didier, it's exactly what i need!!