Previous Year YTD,Prev Month YTD and the rolling 13 months from the selcted

[kalyanswarna]

Hi,I have report to show

MTD, Prev MTD, YTD, PreviousYear YTD and rolling 13 months from the Selected period.

Suppose from my Prompt i selected March 2008 for this i ahve to calculate

MTD----

---

March 2008 Value

Prev MTD----

---

Feb2008 Value

YTD----

---

March 08 ValueFeb08 ValueJAN08

Prev YTD----

---

March 07 ValueFeb07 ValueJAN07

Rolling 13 months---- All the 13 months Value.

Please help me to solve my problem

Thanks,

Kalyan

[kalyanswarna · Accepted solution]

Hi Didier,

Thanks a lot for your reply,Just i Tested the Prev YTD with your solution here its giving the Previous month YTd of the Same Year.

my requirement is for example YTD is MARCH 2008 and my Prev YTD shoud be MARCH2007.

Please see my first post there i explained clearly.

Thanks,

Kalyan

[Former Member · Accepted solution]

Hi,

So, in your case, you "Prev YTD" expression must look like this:

<EXPRESSION> SUM(YTD([MyTimeDimension].lag(12)), [Measure].[MyKeyFigure]) <EXPRESSION>

Didier

[kalyanswarna]

Hi, the Rolling 13 months details i need them Individual

LIke if i choose my prompt as march 2008 the report shuld diaplsy

March2008 YTD------100

FEB2008YTD----

---

200

.....

................

MARCH2007 YTD---59

i need to dispalya this kind of reportcan youplease let me know, how can i do this report.

Thanks alot

[kalyanswarna]

Thanks a lot Didler.

[Former Member]

Hi,

You have to define one calculated measure per calculation you want to use.

So in SAP BW here are some samples on how to achieve them:

MTD:

<EXPRESSION> SUM(MTD([MyTimeDimension].CurrentMember), [Measure].[MyKeyFigure]) <EXPRESSION>

Prev MTD:

<EXPRESSION> SUM(MTD([MyTimeDimension].PrevMember), [Measure].[MyKeyFigure]) <EXPRESSION>

YTD:

<EXPRESSION> SUM(YTD([MyTimeDimension].CurrentMember), [Measure].[MyKeyFigure]) <EXPRESSION>

Prev YTD:

<EXPRESSION> SUM(YTD([MyTimeDimension].PrevMember), [Measure].[MyKeyFigure]) <EXPRESSION>

Rolling 13 months:

<EXPRESSION> Aggregate( [MyTimeDimension].CurrentMember.lag(13): [MyTimeDimension].CurrentMember, [Measures].[MyKeyFigure] )  <EXPRESSION>

You have to take into account that you have to use 0CALYEAR or 0CALMONT or 0CALQUARTER, etc as Time dimension.

This Time dimension does not contain by definition any hierarchy such as: Year/Quarter/Month

Didier

[Former Member]

Hi ,

I am facing similar issue with deriving MTD , YTD values in universe where my datasource is bex query .

I have a measure Loc Currency amount and fiscal/period (2011003)format.

How can i get YTD and MTD ?

I tried with syntax given but it throws error.

<EXPRESSION> SUM(MTD([0FISCPER].[LEVEL01].[[20FISCPER]].[Value].CurrentMember), [Measures].[D7XTNULMVPWMWGGBDFHTDQKNK]) </EXPRESSION>

The xml for the expression is correct however the mdx expression is not parsed in this process.

Please help...

Thanks in advance

priyam

[Former Member]

Hi ,

I am facing similar issue with deriving MTD , YTD values in universe where my datasource is bex query .

I have a measure Loc Currency amount and fiscal/period (2011003)format.

How can i get YTD and MTD ?

I tried with syntax given but it throws error.

<EXPRESSION> SUM(MTD([0FISCPER].[LEVEL01].[[20FISCPER]].[Value].CurrentMember), [Measures].[D7XTNULMVPWMWGGBDFHTDQKNK]) </EXPRESSION>

The xml for the expression is correct however the mdx expression is not parsed in this process.

Please help...

Thanks in advance

priyam

[kalyanswarna]

Hi, Thanks for your quick response but my Source is an SAP BW Query.

i hope the solution you provided will helps only for the RDBMS.

Please let me how can i use MDX for thesethings. i know YTD MDX and MTD and i am struggling with Pre Month MTD and <Mrevious YEAR YTD and the rolling 13 months.

Thanks,

KAlyan

[Former Member]

Sorry Kaylan,

I am uncertain as to whether this can be done in MDX, you will need someelse to update

Regards

Alan

[Former Member]

Hi Kalyan,

A lot will depend on how your source data is structured. For the momemt I will assume that you have a column in the data called financial_period in the format YYYYMM i.e. 200803 for March 2008. As the value you wqant to sum is in an amount field.

MTD is easy enough

Create an object called MTD with code

case when financial_period = @prompt('Enter Fin Period', 'A', .......) then Amount else 0 end

Prev MTD would be

case when to_number(financial_period) = (to_number(@prompt('Enter Fin Period', 'A', .......)) - 1) then Amount else 0 end

YTD would be

case when substr(financial_period, 1, 4)  = substr(@prompt('Enter Fin Period', 'A', .......), 1, 4)  then Amount else 0 end

Prev YTD would be

case when to_number(substr(financial_period, 1, 4))  = (to_number(substr(@prompt('Enter Fin Period', 'A', .......), 1, 4)) -1)  and to_number(substr(financial_period, 5, 2)) <= to_number(substr(prompt('Enter Fin Period', 'A', .......), 5, 2))     then Amount else 0 end

Hope this helps

Alan