MDX expression=Filter data for previous 12 months in BW OLAP universe level

[Former Member]

Hi All,

I am trying to filter data at BW OLAP universe level for previous 12 months .The time period object in BW OLAP Universe is L01 Cal.Year/Month(0calmonth in BW).

Existing MDX expressions in the BW OLAP universe

Rolling 12 Months Avg:

<EXPRESSION>

( SUM({[0CALMONTH].currentmember.lag(11):[0CALMONTH].currentmember},@Select(Key Figures\Rejected Qty))

/

SUM({[0CALMONTH].currentmember.lag(11):[0CALMONTH].currentmember},@Select(Key Figures\Total Quantity))

) * 1000000

</EXPRESSION>

Rolling Month Avg:

<EXPRESSION>

( SUM({[0CALMONTH].currentmember},@Select(Key Figures\Rejected Qty))

/

SUM({[0CALMONTH].currentmember},@Select(Key Figures\Total Quantity))

) * 1000000

</EXPRESSION>

In relation to the 2 MDX statements above I am trying to place a filter on 0CALMONTH to get previous 12 months data only.

If I run the report on the universe today(April/2011) the universe should return data from Apr 2010 to March 2011 only.

Inputs greatly appreciated .

Thanks

[Former Member · Accepted solution]

Use Aggregate() function in place of sum for the Rolling Calculation. Eg:

<EXPRESSION>Aggregate( {[0CALMONTH].CurrentMember:[0CALMONTH].CurrentMember.lead(@Prompt('Numb
er of Months','N',,mono,free))},@Select(Measures\Amount))</EXPRESSION>

Regards,

Rohit