MTD (Month to Date, QTD (Quarter To Date) , YTD (Year To Date),Last three m

[Former Member]

I am new to BW Query based universes. We have the requirements like below:

MTD (Month to Date, QTD (Quarter To Date) , YTD (Year To Date),Last three months (3 mths) rolling average calucaltion.

What avaiable in universe as Dimensions: Fiscal Year(2009), Year Period (022009) and Measure Sales Revenue.

Can I Implement liks tradition universe with regular databases :

Prior Month = Year Period (022009) - 1

Prior Year = Fiscal Year(2009) - 1

Query (RDBMS)

-

---

Select Month, Year , Sales Rev from Tab1 where Year = 2009, Month = 01

and Conditional Object <Prior Month>

My out pur should have

Month Year SalesRev

01 2009 $100

02 2009 $150

How can I achieve the same with OLAP universe with calculcate objects?

Can some body advise on this please.

Pat

[Former Member]

Hi

I have another solution. The variable "YTD" we take 0FISCPER3 and ristrict with the variable 0I_PCURR. It make a structure in the query. Then you can create a filter in the universe. You must refer to the structure's technical name and variable's technical name. In the filter as you do.

Regards

Rikke

[Former Member]

Hi,

You can download some MDX samples [HERE|http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/c0a45246-ce76-2b10-e688-f5c8206203eb].

By the way, here are examples based on your requirements.

YTD: 0CALMONTH characteristic and Net Value key figure

<EXPRESSION>SUM(YTD([0CALMONTH].CurrentMember), [Measures].[0D_NETVAL_S])</EXPRESSION>

QTD: 0CALMONTH characteristic and Net Value key figure

<EXPRESSION>SUM(QTD([0CALMONTH].CurrentMember), [Measures].[0D_NETVAL_S])</EXPRESSION>

Rolling average: choose at runtime the number of periods using 0CALMONTH characteristic and Net Value key figure

<EXPRESSION>AVG(LASTPERIODS(@Prompt('Select number of periods','N',,mono,free), [0CALMONTH].currentmember), [Measures].[0D_NETVAL_S])</EXPRESSION>

Previous Period: 0CALMONTH characteristic and Net Value key figure

<EXPRESSION>([Measures].[0D_NETVAL_S], [0CALMONTH].prevmember)</EXPRESSION>

Regards

Didier

[Former Member]

Hi Diderr,

Thasks for the response and looks usefull. But based on my requirement, The user will key in the range of dates say like 012008 to 122009 (2 years) worth of data.

From this I need to derive last three months & 12months avearge. AlsoMTD based on current month we are in. Secondly YTD, QTD ect from this range of values only.

Please advise further , since in the rolling avg you mentioned different prompt but I need to derive from the same set of values mentoned above.

Thanks

Pat

[Former Member]

Hi,

Can you give more details in spreadheet for instance?

I am not sure to have captured all calculations you want to achieve.

Didier

[Former Member]

Hi Dieder,

Please find the excel sheet with different scenarios explained. Please advise If you need more information.

Thanks

Pat.

[IngoH]

Hi,

the easiest solution for the requirement is to leverage EXIT Variables in the BW query as you don't have something like a system data in the MDX.

Ingo

[Former Member]

Hi Ingo,

Thanks for the response and can you please advise further apart from EXIT variable. Because BW team was asking reporting team to request for required data as well the required format. Say like all time dimensions and calculated measure if any.

In our case Avg 3 months, Rolling Avg 12months, Standard Devitation kind of calculation. I believe where ever possible , we will try to put load on Reporting but the best practices say put load on BW or Universe.

Thks, Pat

[IngoH]

Hi,

you can find more details about EXIT Variables in the BW area here in SDN.

ingo