Greetings,

Things are going well with a newly developed data warehouse and related
cube, suing SQL Server 2005 Standard Edition. All, that is, except
understanding MDX.

I have a time dimension that has a hierarchy as follows:
Calendar Year, Calendar Semester, Calendar Quarter, Month and Date.

I need to calculate the difference (delta) between one of my measures, from
the last period to the current period, where period might be Month, Quarter,
Semester or Year. Having spent a day struggling with this, I have come up
with the following:

WITH
MEMBER [Measures].[ChangeInPNL] AS
'IIF([Dim Time].[Calendar Year - Calendar Semester - Calendar Quarter -
Month - Date].CurrentMember.Level.Ordinal = 0, NULL,
IIF([Dim Time].[Calendar Year - Calendar Semester - Calendar Quarter -
Month - Date].CurrentMember.Level.Name = "Month",
([Measures].[Cumulative PNL],[Dim Time].[Calendar Year - Calendar
Semester - Calendar Quarter - Month - Date].CurrentMember) - (
[Measures].[Cumulative PNL], ParallelPeriod([Dim Time].[Calendar Year -
Calendar Semester - Calendar Quarter - Month - Date].[Month],2)),

IIF([Dim Time].[Calendar Year - Calendar Semester - Calendar Quarter -
Month - Date].CurrentMember.Level.Name = "Calendar Quarter",
([Measures].[Cumulative PNL],[Dim Time].[Calendar Year - Calendar
Semester - Calendar Quarter - Month - Date].CurrentMember) - (
[Measures].[Cumulative PNL], ParallelPeriod([Dim Time].[Calendar Year -
Calendar Semester - Calendar Quarter - Month - Date].[Calendar Quarter])),

IIF([Dim Time].[Calendar Year - Calendar Semester - Calendar Quarter -
Month - Date].CurrentMember.Level.Name = "Calendar Semester",
([Measures].[Cumulative PNL],[Dim Time].[Calendar Year - Calendar
Semester - Calendar Quarter - Month - Date].CurrentMember) - (
[Measures].[Cumulative PNL], ParallelPeriod([Dim Time].[Calendar Year -
Calendar Semester - Calendar Quarter - Month - Date].[Calendar
Semester])),
([Measures].[Cumulative PNL],[Dim Time].[Calendar Year - Calendar Semester
- Calendar Quarter - Month - Date].CurrentMember) - (
[Measures].[Cumulative PNL], ParallelPeriod([Dim Time].[Calendar Year -
Calendar Semester - Calendar Quarter - Month - Date].[Calendar Year]))

)
)
)
)'
SELECT
{ [Dim Analyst].[Analyst Initials].&[AS] } ON ROWS,
{ Descendants([Dim Time].[Calendar Year - Calendar Semester - Calendar
Quarter - Month - Date],[Month]) } ON COLUMNS
FROM
[Performance First Cube]
WHERE
[Measures].[ChangeInPNL]

I believe that I need the IIF clauses to cope with going back one period at
the different levels.

The results are good for all but the Month level. These were very wrong,
until I tried using 2 as an argument in the ParallelPeriod function. With
that, they are better, but still incorrect when comparing months that
straddle a quarter, eg March to April - the April ChangeInPNL shows the
actual value for April, not the difference between March and April. It is as
if 0 is being subtracted.

Can you help explain why I am having to use 2 at all, and how to get the
results correct down to the month level? I amvery new to MDX, so any
guidance will be appreciated.

Also, I imagine there is a shorthand way of dealing with repeated use of
[Dim Time].[Calendar Year - Calendar Semester - Calendar Quarter - Month -
Date]. I tried using SET, but this failed. Any clues?

Many thanks and regards

Sebastian Crewe

Hi Sebastian,

Here's a sample query for Adventure Works, which computes the change in
Order Quantity from the previous period:

iif([Date].[Calendar].CurrentMember.Level.Ordinal = 0
or IsEmpty([Measures].[Order Quantity]), NULL,
[Measures].[Order Quantity] -
([Measures].[Order Quantity],
[Date].[Calendar].PrevMember)),
FORMAT_STRING = '#,#'

select {[Measures].[Order Quantity],
[Measures].[ChangeInOrders]} on 0,
Non Empty {
[Date].[Calendar].[Calendar Year].Members,
[Date].[Calendar].[Calendar Semester].Members,
[Date].[Calendar].[Calendar Quarter].Members,
[Date].[Calendar].[Month].Members} on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I think you may be over complicating things a little here. If all you
want to do is to subtract the current member from the previous member on
the same level, all you would have to do is something like the
following.


MEMBER [Measures].[ChangeInPNL] AS '([Measures].[Cumulative PNL],[Dim
Time].[Calendar Year - Calendar Semester - Calendar Quarter - Month -
Date].CurrentMember) - ([Measures].[Cumulative PNL],[Dim Time].[Calendar
Year - Calendar Semester - Calendar Quarter - Month -
Date].CurrentMember.PrevMember)'

ParallelPeriod is used in situations where you want to get the same
day/month/year member from last year (or some other parallel period).

I'm not sure what the issue is with April, but one thing I would double
check is the ordering of your month attribute. If the months are sorted
by name instead of by month number, then you would get strange results
and you would get a 0 for April as it will sort first by name.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <D42B62C8-359E-4218-A8C5-99525EC0AB58 (AT) microsoft (DOT) com>,
SebastianCrewe (AT) discussions (DOT) microsoft.com says...

Many thanks indeed for your swift and helpful reply. I felt that I was
over-complicating things but wasn't having much luck with my novice stabs at
getting the syntax right.

Using your example of PrevPeriod still took a bit of manipulation to get
working, more I think because of line breaks and the like. Anyway, it does
now work.

And your guess as to the Month problem was spot on. I had to sort the
MonthName attribute using MonthNumberOfYear, and followed the very good
'Sorting Attribute Members Based on a Secondary Attribute' topic in SQL 2005
BOL to do so. Calculations are now correct, using either ParallelPeriod or
your more concise PrevPeriod. I will save the former for future use.

Great result. Thank you for taking the time to consider my question with
care.

Sebastian

"Darren Gosbell" wrote: