US and Worldwide: +1 (866) 660-7555

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

1. nko
Senior Member
Join Date
Mar 2010
Posts
173

## 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. nko
Senior Member
Join Date
Mar 2010
Posts
173
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. Senior Member
Join Date
Apr 2008
Posts
2,467
Have you tried the functions from:
http://www.databasejournal.com/featu...-Functions.htm

4. nko
Senior Member
Join Date
Mar 2010
Posts
173
Sorry, but I don't understand which function I have to use among those you posted to me!

5. Senior Member
Join Date
Apr 2008
Posts
2,467
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.

6. nko
Senior Member
Join Date
Mar 2010
Posts
173
Thanks gutlez.... I'll try the solution you propose!

Regards

7. Junior Member
Join Date
Jul 2013
Posts
12
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. Senior Member
Join Date
Apr 2008
Posts
2,467
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.