Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Reset Counter using MDX

Answered Reset Counter using MDX

  • Thursday, May 31, 2012 9:57 AM
     
     
    Sign In to Vote
    0

    Hello,

    I am trying to generate a counter for a given measure based on +ve or-ve value, counter should reset when the measure value changes from positive to negative vice-versa, see below sample on 7 day value is changed to +ve and counter is reset to 1 on 11 day values is again becomes –ve and counter is reset to -1 

     

    COBDay

        Value

         Count

    COBDay 

    1

    -10000

    -1

    COBDay 

    2

    -9999

    -2

    COBDay 

    3

    -9998

    -3

    COBDay 

    4

    -9997

    -4

    COBDay 

    5

    -9996

    -5

    COBDay 

    6

    -9995

    -6

    COBDay 

    7

    10000

    1

    COBDay 

    8

    10001

    2

    COBDay 

    9

    10002

    3

    COBDay 

    10

    10003

    4

    COBDay 

    11

    -10000

    -1

    COBDay 

    12

    -10000

    -2

    COBDay 

    13

    10000

    1
      

    Any help would be really appreciated.

     

All Replies

  • Friday, June 01, 2012 12:18 PM
     
     Answered
    Sign In to Vote
    0

    Here is the query that you can use.   Please be aware that counter will increment for missing dates as well.

    WITH
      MEMBER [Measures].[CanReset] AS
        IIF
        (([Measures].[Score] < 0   AND ([Date].[Date].CurrentMember.PrevMember,[Measures].[Score]) > 0)
    OR
    ([Measures].[Score] > 0   AND ([Date].[Date].CurrentMember.PrevMember,[Measures].[Score]) < 0)
         ,1     ,0  )

    MEMBER [Measures].[CumSum] AS IIF([Measures].[CanReset] = 1,1, ([Date].[Date].CurrentMember.PrevMember,[Measures].[CumSum])+1 )
    MEMBER [Measures].[CumSumFinal] AS  IIF([Measures].[Score] < 0,[Measures].[CumSum] * -1,[Measures].[CumSum])
    SELECT
       {[Measures].[Score],[Measures].[CumSumFinal]  } ON COLUMNS
     , [Date].[Date].[Date].AllMembers   ON ROWS
    FROM [Cube];

    • Marked As Answer by Kumar2Vinod Monday, June 04, 2012 6:40 AM
    •  
     
  • Monday, June 04, 2012 6:42 AM
     
     
    Sign In to Vote
    0
    Thanks Anand, it worked I have to change the query a bit as my Date dimension is ordered in descending order.

    Regards, Vinod

     
  • Monday, June 04, 2012 8:57 AM
     
     
    Sign In to Vote
    0
    not sure how to convert this into calculated member? Any help would be really appreciated.

    Regards, Vinod

     
  • Tuesday, June 26, 2012 8:35 AM
     
     Answered
    Sign In to Vote
    0

    For converting it into Calculated member I have broken query into 3 parts

     #1 [CumSum]

    MEMBER [Measures].[CumSum] AS IIF([Measures].[CanReset] = 1,1, ([Date].[Date].CurrentMember.PrevMember,[Measures].[CumSum])+1 )

    #2 .[CumSumFinal] 

        MEMBER [Measures].[CumSumFinal] AS  IIF([Measures].[Score] < 0,[Measures].[CumSum] * -1,[Measures].[CumSum])

    #3 [CanReset] only 3 is visible 

    IIF
        (([Measures].[Score] < 0   AND ([Date].[Date].CurrentMember.PrevMember,[Measures].[Score]) > 0)
    OR 
    ([Measures].[Score] > 0   AND ([Date].[Date].CurrentMember.PrevMember,[Measures].[Score]) < 0)
         ,1     ,0  )


    Regards, Vinod

    • Marked As Answer by Kumar2Vinod Tuesday, June 26, 2012 8:35 AM
    •