Cumulative Caclulation in MDX

Question

Can i calculate a cumulative value without a datefield. I have fact which has no date but i would need to generate cumulative of that measure using a dimensional attribtute ? how can i accomplish this ?

Can this be done in SSAS

Answer 1

I have no date associated with it. I need to calculate based on dimension attribute. In this case i have dimension from 1 to

Project 1	$1	$1
Project 2	$1	$1
Project 1	$2	$3
Project 1	$2	$5
Project 1	$3	$8
Project 1	$4	$12
Project 1	$4	$16

Answer 2

“Can i calculate a cumulative value without a datefield.?”

 

-       You don’t need the date dimension. You can create a calculated member with any normal dimension attributes. For example, try the below one:

 

WITH

  MEMBER Measures.[Cum Sales] AS

    Sum

    (Head

      ( Order

        ([Customer].[Customer].Siblings

         ,Measures.[Unit Sales]

         ,BDESC

        ) AS [AliasSet]

       ,Rank

        ([Customer].[Customer].CurrentMember

         ,[AliasSet]

        )

      ),Measures.[Unit Sales]

)

 

Note the order of the set you calculated.

 

Regards,

Raymond

Answer 3

You mean you want to take the Cumulative value as the dimension members? If so, I don’t think you can achieve that through calculations. You need to create a view/named query with T-SQL, create Cumulative column, and then generate the dimension from the view/named query.

 

Regards,

Raymond

Answer 4

I have the cost which is a integer column in dimension , Does that mean , that i cannot achieve a cumulative for the rows in dimension as an additional column untill i hav sql view or named calculation.

Can't  a scope statment assign a value to dimension attribute  ?

Scope Dimension.Attribute = Measure.Cumsales

Answer 5

“Can't  a scope statment assign a value to dimension attribute  ?”

-       You cannot. The Scope statement has to specify a subcube, not an attribute name. I mean you can only overwrite the value of the query, not the dimension name. Just like  you cannot write like this:Scope ( [Date].[Calendar Year].&[2001].name)

-         

Regards,

Raymond