Dynamic Period Over Period Growth Without Hierarchy in Time/Date Dimension?

The time dimension in our AS2005 cube is not hierarchical. Therefore, how can I dynamically do a period over period growth calculated member? I'd like it to determine the growth whether the user is viewing it yearly, quarterly or monthly.

If our time dimension was indeed hierarchical, I would define the metric as follows:

([Time].[Currentmember], [Measures].[Sales] - ([Time].[CurrentMember].[PrevMember], [Measures].[Sales])

Right now, I have only been able to do it for a specific level whether it's year, quarter or month. For example, for a year over year growth, I've defined it as follows:

([Date].[Year].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Year].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

If I wanted to do the same for months, I'd replace "Year" with "Month" as follows:

([Date].[Month].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Month].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

Is it possible to do a dynamic period over period growth in 1 calculated member based on how our Date dimension is setup?

Could you explain what attribute relations exist in your time dimension - are quarters related to months, and years to quarters? Or is the [Month] like a "month-of-year" and quarter like a "quarter-of-year"? In that case, you could try a scoped assignment like:

Create [Measures].[SalesGrowth];

Scope([Measures].[SalesGrowth]);

Scope([Date].[Year].[Year]);

this = [Measures].[Sales USD] - ([Date].[Year].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Quarter].[Quarter]);

this = [Measures].[Sales USD] - ([Date].[Quarter].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Month].[Month]);

this = [Measures].[Sales USD] - ([Date].[Month].PREVMEMBER, [Measures].[Sales USD]);

End Scope;

End Scope;

End Scope;

End Scope;

|||Deepak,

I've never used SCOPE before, but this looks like it could work. Where do I use SCOPE? In the definition of the [SalesGrowth] calculated member? Or do I have to somehow make use of a new SCRIPT command? I've never done this either. The only Script Command I have is at the default "CALCULATE". that goes before all of my calculated members.

Thanks!
|||The code above already includes a statement to create [SalesGrowth], at the beginning - you could append this snippet to your existing script, after the other calculated members.|||Deepak,

Thanks for exposing me to SCOPE! I was able to acheive what I wanted with just a few tweaks.

Dynamic ParallelPeriode

Hi,

with this MDX expression as a calculated member in my cube

100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Month], 12), [Measures].[Sales Volume KG])

I get the difference of the Sales Volume from this month and this month last year. But how can I make it dynamic for drilldown in the Time Dimension, so that also year, semester, Quarters and so on are supported? I will compare this year - last year, this semester - same semester last year, this quarter - same quarter last year and so on.

Thanks

Hans

Hi Hans,

Why doesn't ParallelPeriod(Year ..) meet your needs - could you explain in the context of this Adventure Works query?

>>

select

{[Measures].[Sales Amount]} on 0,

Generate(Ascendants([Date].[Calendar].[Date].&[800]),

{[Date].[Calendar].CurrentMember,

ParallelPeriod([Date].[Calendar].[Calendar Year],

1, [Date].[Calendar].CurrentMember)}) on 1

from [Adventure Works]

-

Sales Amount
September 8, 2003 $36,027.71
September 8, 2002 $18,755.92
September 2003 $5,057,832.17
September 2002 $3,235,826.19
Q3 CY 2003 $13,670,536.57
Q3 CY 2002 $10,277,073.06
H2 CY 2003 $26,955,981.04
H2 CY 2002 $18,646,056.13
CY 2003 $41,993,729.72
CY 2002 $30,674,773.18
All Periods $109,809,274.20

>>

|||

Hi Deepak,

Thanks for your suggestions. Your code gave me the idea to specify the Dimension in full, so I name it [Date].[Periode - Year].[Year] and not only [Date].[Year] and now it works. But it works only with the [Periode - Year] Hierarchy. How can I change it, to work with all my Time hierarchies ([Date].[Periode - Year], [Date].[Periode - Week], [Date].[Periode - Reporting])? I tried

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Periode - Year].[Year], 1) * parallelperiod([Date].[Periode - Week].[Year], 1) * parallelperiod([Date].[Periode - Reporting].[Year], 1), [Measures].[Sales Volume KG])) )

but that doesn't work. You gave me this approach just a year ago for builing YTD sums just like

IIF( sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]) = 0, 0, sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Commission To Market]) / sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]))

which work fine, but this doesn't work here. How can I do it here?

Thanks

Hans

|||

Hi Hans,

With ParallelPeriod() a different approach is needed, which would depend on the attributes in the 3 hierarchies and the attribute relations (an entry in Mosha's blog shows some examples). If all the hierarchies align with the beginning of the time dimension (which isn't true for Adventure Works Calendar hierarchy), then a cube MDX script assignment like this might work - otherwise, you could describe the attributes and hierarchies in more detail:

([Measure].[SalesGrowth], [Date].[Date].Members) =

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG]

* (parallelperiod([Date].[Periode - Year].[Year]), [Measures].[Sales Volume KG])) );

dynamic mdx

Hello i'm new user,

i have a calculated member

the function LINREGSLOPE juste calculate the slope ( ie: a) of the equation Y= a *X+b

calculated member Name= Gain

LINREGSLOPE([TIME].[Month].&[2006-02-01T00:00:00]:[TIME].[Month].&[2006-06-01T00:00:00],[Mesures].[NumofCustomer],[TIME].[Month], [TIME].[Rank])

i write

the formula do the job veru well. What i want to do now is to

transform the expression to be dynamic with two parameter

I hope this will help.

I seen in this forum that this is possble. how to extract

this information. How can i concat the parametre!month.value with the

string ?

i see idea of solution here but i dont know how to exploit it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=443431&SiteID=1

how can i do this please help

thanks

hi,

If I am not mistaken, I think you want to restrict the query based on static value instead of dynamic parameters. Right? If that is the case you can just include a where condition to your query like this:

where [Dimension].[Heirarchy].&[Member1]:[Dimension].[Heirarchy].&[Member2]

Note: Dimesion is your dimension name

Heirarchy is your heirachy name.

Member1 is the start date

Member 2 is the end date

I hope this will help.

Sincerely,

Amde

|||thanks amde,

no it's the opposite from static de dynamique.

if the user change the parametre the query will change too.i want to write my expresion with a parameter.

i want somethink like this:

linregslope([Time].[month].&"param1":[Time].[month].&"param2",[measures].[numberCustomer],[Time].[Month].[MonthNumber]).

if you have others questions feel free to ask me.

visite the link in my first post.

I hope this will help.

any idea is welcome

casagrandi

|||

ok thanks

i found the solution

this isi the code:

WITH

set [RangeMonth] as LastPeriods(STRTOMEMBER (@.FromTIMEMonth,CONSTRAINED),STRTOMEMBER (@.ToTIMEMonth,CONSTRAINED))

MEMBER [Measures].[GAIN PER MONTH] AS 'LINREGSLOPE([RangeMonth],[Measures].[mymeasure],Rank([TIME].[Month], [TIME].[Month].Members))'

,format="percent"

SELECT NON EMPTY {[Measures].[GAIN PER MONTH],[Measures] ..........

sql