MS SQL Server OLAP - Measures from previous Periods

Author

Measures from previous Periods

Jens Feuring

2005-11-27, 8:24 pm

Hi...

I´m new in mdx-programming and i have to find a solution for the following
problem... hope someone here can help me:

i need a mdx statement for a calculated element "prev_netto" to display
the value of a measure "netto" of a previous period (ex: the year 2004).

maybe this short table helps to explain:

year netto prev_net
to
2002 10.000
2003 20.000 10.000
2004 25.000 20.000
2005 30.000 25.000

the following also must be achieved:
country netto prev_
netto
US 3.000.000 2.000.000
CH 1.000.000 1.000.000
GB 2.000.000 1.000.000

thank you for any help!

Deepak Puri

2005-11-28, 3:24 am

Assuming that there is a [Time] dimension hierarchy with a [Year] level,
then try ParallelPeriod(), like:
[color=darkred]
Member [Measures].[prev_netto] as
'([Measures].[netto], ParallelPeriod([Time].& #91;Year]))'[color=d
arkred]

http://msdn2.microsoft.com/en-us/library/ms145500(en-US,SQL.90).aspx[color=darkred]
ParallelPeriod (MDX)

Returns a member from a prior period in the same relative position as a
specified member.
...[color=darkred]

If you're using AS 2005, you can also add time period comparision
capabilities by defining Time Intelligence:

http://msdn2.microsoft.com/en-us/library/ms175440.aspx[color=darkred]
Defining Time Intelligence (SSAS)

The time intelligence enhancement is a cube enhancement that adds time
calculations (or time views) to a selected hierarchy. This enhancement
supports the following categories of calculations:

Period to date.

Period over period growth.

Moving averages.

Parallel period comparisons.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Jens Feuring

2005-11-28, 7:24 am

Thank you - you exactly hit the point... :-)

> Member [Measures].[prev_netto] as
> '([Measures].[netto], ParallelPeriod([Time].[Year]))'

Sponsored Links