Results 1 to 6 of 6

Thread: Previous Year Comparison

  1. #1
    Join Date
    Jan 2006
    Location
    Cyprus
    Posts
    4

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Linkks to Working Examples ...

    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

  3. #3
    Join Date
    Jan 2006
    Location
    Cyprus
    Posts
    4
    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

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    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

  5. #5
    Join Date
    Jan 2006
    Location
    Cyprus
    Posts
    4
    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 !

  6. #6
    Join Date
    Jan 2006
    Location
    Cyprus
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •