Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Arrow Unanswered: Nulls and Lags in MDX

    Friends ,



    I have been struggling to find a solution for nulls and lags , please help with a solution



    i need to get a average of 3 months , for which i have used lag function , but apparently, the values in 1st two months is null for which its not giving me the correct results.



    While i am using this code , i get the following results, which are not correct



    AVG(

    [Tbl Date Key].[Hierarchy].CurrentMember.lag(2):[Tbl Date Key].[Hierarchy].CurrentMember,

    [Measures].[Q1_Avg])



    i get the following result which is not acceptable



    Level 04 Level 05

    Region CE
    District CE1

    Calendar Year Calendar Month Ke Date Q1_Avg try
    2007 1 .00 0.00
    2 .00 0.00
    3 950.00 316.67
    4 955.61 635.20
    5 949.82 951.81
    6 939.81 948.41
    7 945.25 944.96
    8 945.04 943.37
    9 945.51 945.27
    10 955.89 948.82
    11 947.24 949.55
    12 946.61 949.92
    Total 947.47 947.47
    Grand Total 947.47 947.47





    The Actual Result should be





    Calendar Year Calendar Month Ke Date Q1_Avg try
    2007 1 .00 0.00
    2 .00 0.00
    3 950.00 950.00
    4 955.61 952.81
    5 949.82 951.81
    6 939.81 948.41
    7 945.25 944.96
    8 945.04 943.37
    9 945.51 945.27
    10 955.89 948.81
    11 947.24 949.55
    12 946.61 949.91
    Total 947.47 947.47
    Grand Total 947.47 947.47




    Please advise with can be done on this code

  2. #2
    Join Date
    Mar 2008
    Posts
    1
    Maybe this MDX FAQ entry will help you?
    http://www.ssas-info.com/analysis-se...g-empty-months

Posting Permissions

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