US and Worldwide: +1 (866) 660-7555
Results 1 to 8 of 8

Thread: Select two preivuous years and next year from given date

  1. #1
    Join Date
    Mar 2010
    Posts
    173

    Default Select two preivuous years and next year from given date

    Hi everibody, I need your help on design MDX query.

    I want to compute the difference in payments respect to a given date: sum(payment of two last years) - sum(payment on next year) and shows it by quarter.

    Maybe I'll have to calculate two different MEMBER, "PREV TWO YEARS" and "NEXT YEAR" respect to the given date and then make a difference between them....

    PREV TWO YEARS <-- SELECTED DATE --> NEXT YEAR


    Which is the best way to do this?

    Many thanks.

    Nico

  2. #2
    Join Date
    Mar 2010
    Posts
    173

    Default

    I found the solution myself... using the function Lag() which allow to subract/add days (basic level of my hierarchy from the given date) in the way exposed below:

    with
    member [measures].[PREV] as (SUM({[dATE].[Dat].&[2013-07-25].Lag(730):[dATE].[Dat].&[2013-07-25]}, [Measures].[x]))

    member [measures].[NEXT] as (SUM({[Date].[Dat].&[2013-07-25].Lag(-365):[Date].[Dat].&[2013-07-25]}, [Measures].[X]))

    member [measures].[NET] as ([measures].[PREV] - [measures].[NEXT])
    select

    NON EMPTY[measures].[NET] on 0,
    NON EMPTY [Dat].[QUARTER].Members on 1

    FROM my cube
    It works fine if I don't aggregate on quarters, but if I do that it is very slow... is there a way to optimize such kind of MDX query?

    THANKS,

    Nico

  3. #3
    Join Date
    Apr 2008
    Posts
    2,467

    Default

    Have you tried the functions from:
    http://www.databasejournal.com/featu...-Functions.htm
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  4. #4
    Join Date
    Mar 2010
    Posts
    173

    Default

    Sorry, but I don't understand which function I have to use among those you posted to me!

  5. #5
    Join Date
    Apr 2008
    Posts
    2,467

    Default

    If you set up your Date as a multilevel date-type hierarchy, then you can use
    [Date].[Year].NextMember as a dimension...
    [Date].[Year].PrevMember as another

    Then you get:
    Current Year Prev Year Next Year
    Measure 5 2 3

    Just a thought - Can't say that it's going to do the things you want it to.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  6. #6
    Join Date
    Mar 2010
    Posts
    173

    Default

    Thanks gutlez.... I'll try the solution you propose!

    Regards

  7. #7
    Join Date
    Jul 2013
    Posts
    12

    Default

    There are really plenty of functions available at the website but i can't decide which function to use actually..that is pretty much confusing.

  8. #8
    Join Date
    Apr 2008
    Posts
    2,467

    Default

    Amber,

    Since your use is likely not identical to Nicola's, we can't tell you which function to use.
    If you start a new thread explaining what you are trying to accomplish (wait for it - it will post after moderation!), we can work through it and figure out what the best choice is.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

Tags for this Thread

Posting Permissions

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