Previous Year Comparison

Hi,

I'm new to MDX. I am trying to create a calculated member to give me a comparison with the same time period from the previous year.

I have been looking at the ParallelPeriods function but I am struggling to get the syntax correct.

I have a Time dimension (Year > Month > Date) and the measure I am want to use is called "Net Sales".

Can anybody help ?

Regards
Carl

Hi:

Take a look at this article for some working examples on the FoodMart cube:

http://www.databasejournal.com/article.php/1459531

In addition, browse through my articles index -

http://www.databasejournal.com/article.php/1459531

- specifically through the MDX Essentials series - to see alternative means of handling your needs.

Good Luck!

Bill

Bill,

Thanks for the directions. Looks very comprehensive. I have now upgraded to 2005 so I will have another go and let you know how I get on.

Cheers

Carl

Great. Keep in mind that, although MDX is extended, and there are new ways of interacting within the Studios, etc. (like we do in my more recent articles), the vast majority of the MDX Essentials series will remain quite relevant...

Good Luck.

Bill

I hope somebody can give me a nudge in the right direction.

I am working on VS2005 on SQL Server 2005.

I have created a simple test cube and I am trying to create extra calculated measures for "Year to Date" and "Previous Year".

I have managed the Year To Date with :-

Sum({Ytd()} , ([Measures].[Trn Lin Net Sales]))

This works well across the time dimension

I am still having problems with the syntax for ParallelPeriods. I have tried :-

1. ([Measures].[Trn Lin Net Sales], ParallelPeriod ([Time].[Year]))

2. ([Measures].[Trn Lin Net Sales], ParallelPeriod ([Time Dimension].[Tim_Year]))

3. ([Measures].[Trn Lin Net Sales], ParallelPeriod ([Time Dimension].[Tim_Year],1))

4. ([Measures].[Trn Lin Net Sales], ParallelPeriod ([Time Dimension].[Tim_Year],1, [time dimension].currentmember))

5. {[Measures].[Trn Lin Net Sales], ParallelPeriod ([Time Dimension].[Tim_Year],1, [time dimension].currentmember)}

and many many more but all I get is #Value!

Please help !

Solved. :)

For the many people who are fighting with MDX :

My final MDX for the calculated member is

(PARALLELPERIOD( [Time Dimension].[Tim_Year - Tim_Date].[Tim_Year], 1,[Time Dimension].[Tim_Year - Tim_Date].currentmember ), [Measures].[Trn Lin Net Sales])

[Tim_Year - Tim_Date] is my simple time hierachy.

Thanks William for those articles. I had to go through half of them !!

Carl