Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003

    Unanswered: Last available Value from Last 'n' Years(OLAP, MDX)


    We have a requirement in which data for a member if not avbl. need to be replaced by the latest data avbl. in the last 'n' previous members and we need to control the number of previous members('n') that can be traversed.

    if we are querying data for 1999 and we give traversal parameter of 3 yrs, in case of non avbl. data for 1999, the next avbl data between 1999-1997 should be placed there. In case of traversal year being 4 then the search range changes to 1999-1996.

    Year-------- DataSet1 -------- DataSet2
    1996-------- 1 ---------------- 1
    1997-------- N/a ---------------- 2
    1998-------- N/a ---------------- N/a
    1999-------- N/a ---------------- N/a

    Result Set -------- YEAR -------- (Traverse 3years) --------(Traverse 4years)

    Data Set 1 -------- 1999 -------- N/a -------- ---------------- 1
    Data Set 2 -------- 1999 -------- 2 -------- ---------------- 2

    We used the fallowing query...but it is throwing the recursion error.

    With Member [Measures].[Last Updated]AS 'tail(
    Filter ( LastPeriods(3,[Time].Currentmember),
    Not isEmpty([Measures].[Observation Value])

    Member [Measures].[Last Updated1]AS'
    iif (NOt isEmpty([Measures].[Observation Value]),[Measures].[Observation Value],([Measures].[Last Updated]))'

    Crossjoin ({[Series].[All Series].[Current account].[BM.GSR.FCTY.CD],[Series].[All Series].[Travel & tourism].[ST.INT.XPND.MP.ZS]},{[Measures].[Observation Value],[Measures].[Last Updated1]}) on columns,
    ({[Time].[All Time].[1996],[Time].[All Time].[1997],[Time].[All Time].[1998],[Time].[All Time].[1999]}) on rows
    from Test1
    where([Country].[All Country].[IND])

    Any help is highly appreciated.

    Thanks and regards

    Last edited by sbutchi; 11-11-03 at 14:09.

  2. #2
    Join Date
    Nov 2002
    What is N/A? Null?

    Also, you're trying to mix and match you want the MAX?

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Thaks for responding..

    1. N/A is Not Available. It means for these combination of dimensions there is no data.

    2. I am writing a query like, for each member what is the value available in cube and what value it has been replaced with (in case of n/a cases).

    Originally posted by Brett Kaiser
    What is N/A? Null?

    Also, you're trying to mix and match you want the MAX?

Posting Permissions

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