none
Aggregate over dimension RRS feed

  • Question

  • I need to create a calculated member to aggregate data over a time dimention but the value must be incremental.

    EX.

    Measure = In-Inventory,
    Dim= Week
    Calutated Member = Acumulate

    Week1 --> In-Inventory Value = 10 , Acumulated = 10
    Week2 --> In-Inventory Value = 8, Acumulated = 18
    Week3 --> In_Inventory Value = 5, Acunulated = 23

    And so

    Any Ideas?

     

    Thursday, August 4, 2011 1:37 PM

Answers

  •  

    Hi X_Pancho,

    It seems you want to achieve runningvalue function in SSAS using MDX, right? If that is the case, please refer to below sample MDX based on AdventureWorks database and the result returned from the MDX:

     

    WITH MEMBER [Measures].[Cumulative Sales] AS 
    
     SUM({null: [Date].[Calendar Week of Year].CurrentMember},[Measures].[Sales Amount]) 
     
    
    SELECT {[Measures].[Sales Amount],[Measures].[Cumulative Sales]} ON 0, 
    
     NONEMPTY( [Date].[Calendar Week of Year].Members,[Measures].[Sales Amount]) ON 1 
    
    FROM [Adventure Works] 
    
     

    Sales Amount

    Cumulative Sales

    All Periods

    $109,809,274.20

    $109,809,274.20

    CY Week 1

    $3,952,312.29

    $3,952,312.29

    CY Week 2

    $540,528.04

    $4,492,840.32

    CY Week 3

    $559,479.46

    $5,052,319.79

    CY Week 4

    $543,635.82

    $5,595,955.60

    CY Week 5

    $4,814,061.15

    $10,410,016.75

    CY Week 6

    $3,351,413.58

    $13,761,430.33

    CY Week 7

    $604,721.74

    $14,366,152.07

    CY Week 8

    $637,009.29

    $15,003,161.36

    CY Week 9

    $3,575,005.25

    $18,578,166.61

    CY Week 10

    $3,329,603.71

    $21,907,770.32

    CY Week 11

    $629,231.50

    $22,537,001.82

    CY Week 12

    $579,968.11

    $23,116,969.93

    CY Week 13

    $569,684.54

    $23,686,654.47

    .............

    If you have any question, please feel free to let us know.

    Thanks,
    Challen Fu 

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Tuesday, August 9, 2011 9:40 AM
    Moderator

All replies

  • Try this:

    WITH MEMBER [Measures].[Acunulated] AS
    Sum( PeriodsToDate(
    <<Time Dim>>.<<Time Hierarchy>>.<<Year Level>>,
    <<Time Dim>>.<<Time Hierarchy>>.CurrentMember
    ), [Measures].[In-Inventory])

    SELECT {[Measures].[In-Inventory], [Measures].[Acunulated]} ON 0,
    <<Time Dim>>.<<Time Hierarchy>>.<<Week Level>>.MEMBERS ON 1
    FROM <<Cube Name>>

     

     


    -Remember to mark as helpful/the answer if you agree with the post.
    Thursday, August 4, 2011 1:51 PM
  • Hi X_Pancho,

    Could you please clarify whether this incremental value would reset anytime or not? In another words will this value will increment for ever since it's inception? Or do you need YTD type of calculation?

    Regards,
    Santosh

    Thursday, August 4, 2011 1:53 PM
  •  

    Hi X_Pancho,

    It seems you want to achieve runningvalue function in SSAS using MDX, right? If that is the case, please refer to below sample MDX based on AdventureWorks database and the result returned from the MDX:

     

    WITH MEMBER [Measures].[Cumulative Sales] AS 
    
     SUM({null: [Date].[Calendar Week of Year].CurrentMember},[Measures].[Sales Amount]) 
     
    
    SELECT {[Measures].[Sales Amount],[Measures].[Cumulative Sales]} ON 0, 
    
     NONEMPTY( [Date].[Calendar Week of Year].Members,[Measures].[Sales Amount]) ON 1 
    
    FROM [Adventure Works] 
    
     

    Sales Amount

    Cumulative Sales

    All Periods

    $109,809,274.20

    $109,809,274.20

    CY Week 1

    $3,952,312.29

    $3,952,312.29

    CY Week 2

    $540,528.04

    $4,492,840.32

    CY Week 3

    $559,479.46

    $5,052,319.79

    CY Week 4

    $543,635.82

    $5,595,955.60

    CY Week 5

    $4,814,061.15

    $10,410,016.75

    CY Week 6

    $3,351,413.58

    $13,761,430.33

    CY Week 7

    $604,721.74

    $14,366,152.07

    CY Week 8

    $637,009.29

    $15,003,161.36

    CY Week 9

    $3,575,005.25

    $18,578,166.61

    CY Week 10

    $3,329,603.71

    $21,907,770.32

    CY Week 11

    $629,231.50

    $22,537,001.82

    CY Week 12

    $579,968.11

    $23,116,969.93

    CY Week 13

    $569,684.54

    $23,686,654.47

    .............

    If you have any question, please feel free to let us know.

    Thanks,
    Challen Fu 

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Tuesday, August 9, 2011 9:40 AM
    Moderator