locked
LAG MDX statement RRS feed

  • Question

  • I have an MDX statement to return a prior period result

    ([Dim Time].[FullDateAlternateKey].CurrentMember.Lag(1), [Measures].[EOD Book Balance])

    which successfully returns the previous day's balances for a selected date

    My question is this - is it possible to add something to / or replace the LAG number so that this value is somewhat dynamic. I was thinking that i could do this

    ([Dim Time].[FullDateAlternateKey].CurrentMember.Lag([Dim Time].[DayNumberofYear]), [Measures].[EOD Book Balance])

    So as to return the Year "Opening Balance". But that didn't work - can you do this ? Or am I barking up the wrong function ?

    Monday, March 26, 2007 2:59 PM

Answers

  • Lag() takes a numeric argument, so if the attribute: [Dim Time].[DayNumberofYear] has an integer member value defined, you could use: .Lag([Dim Time].[DayNumberofYear].MemberValue). For example, the Adventure Works DayOfYear attribute has an integer value:

    >>

    With

    Member [Measures].[BegOfYear] as

    [Date].[Calendar].Lag(

    [Date].[Day of Year].MemberValue - 1).MemberValue

    select

    {[Measures].[BegOfYear]} on 0

    from [Adventure Works]

    where [Date].[Calendar].[February 1, 2002]

    -----------------------------------------------------------------------------

    BegOfYear
    1/1/2002

    >>

    Monday, March 26, 2007 3:42 PM
  • This really is another topic in itself; but if the the "user-friendly" string is the name of the member, then the ampersand isn't needed:

    With

    Member [Measures].[BegOfYear] as

    ([Time].[Day of Year].Lag(

    [Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

    select

    {[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

    from [DailyBalances]

    where [Time].[Simple Date].[Jan 1, 2007]

    Monday, March 26, 2007 9:37 PM

All replies

  • Lag() takes a numeric argument, so if the attribute: [Dim Time].[DayNumberofYear] has an integer member value defined, you could use: .Lag([Dim Time].[DayNumberofYear].MemberValue). For example, the Adventure Works DayOfYear attribute has an integer value:

    >>

    With

    Member [Measures].[BegOfYear] as

    [Date].[Calendar].Lag(

    [Date].[Day of Year].MemberValue - 1).MemberValue

    select

    {[Measures].[BegOfYear]} on 0

    from [Adventure Works]

    where [Date].[Calendar].[February 1, 2002]

    -----------------------------------------------------------------------------

    BegOfYear
    1/1/2002

    >>

    Monday, March 26, 2007 3:42 PM
  • Cheers, I'll try that & get back to you.

    I had tried .CurrentMember with no success.... MemberValue hadn't occurred to me (!)

    Monday, March 26, 2007 3:54 PM
  • OK, we're really getting somewhere - I used this

    With

    Member [Measures].[BegOfYear] as

    ([Time].[Day of Year].Lag(

    [Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

    select

    {[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

    from [DailyBalances]

    where [Time].[Day of Year].&[23]

    Which gives me the correct result, however I'd really want to pass a date parm to the WHERE clause - unfortunately this produces an error... do you know why ??

     

    Monday, March 26, 2007 7:31 PM
  • Well, it depends on what the MDX looks like with the parameter, and what value is being passed in. Are you using Reporting Services - if so, what's the data type of the parameter?
    Monday, March 26, 2007 7:39 PM
  • Well, I guess what i'd envisage is the ability for the user to select a date, i.e. Jan 31 2007 (this would be a string format as per the "friendly date" style of construct on MSDN here

    So i'd end up with something like

    With

    Member [Measures].[BegOfYear] as

    ([Time].[Day of Year].Lag(

    [Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

    select

    {[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

    from [DailyBalances]

    where [Time].[SImple Date.&[Jan 1, 2007]

    This though would I guess throw an error, so is there anyway to pass the relevant "Day of Year" to the MDX even if the user selects my "friendly date" from a dropdown ?

    Monday, March 26, 2007 8:04 PM
  • This really is another topic in itself; but if the the "user-friendly" string is the name of the member, then the ampersand isn't needed:

    With

    Member [Measures].[BegOfYear] as

    ([Time].[Day of Year].Lag(

    [Time].[Day of Year].MemberValue - 1),[Measures].[EOD Book Balance])

    select

    {[Measures].[BegOfYear],[Measures].[EOD Book Balance]} on 0

    from [DailyBalances]

    where [Time].[Simple Date].[Jan 1, 2007]

    Monday, March 26, 2007 9:37 PM