MDX: Sum Across a Diagonal (not the row or column)

hi,

i trying use mdx sum across diagonal of cube.  example, if dataset looks like:

postmonth........1..............2.............3..................4......................5

jan 2010......$10............$15..........$20

feb 2010.....$25............$30...........$35..............$40

mar 2010....$45............$50............$55..............$60................$65

 

in row "mar 2010", column "5", want show value $125 ($20 + $40 + $65). in row "feb 2010", column "4", want show value $60 ($20 + $40). in row "mar 2010", column "4", want show value $110 ($15 + $35 + $60).  thing can think of along these lines:

 

member [test] as

[arbalance] +

 

( [arbalance],

[ar bucket dim].[ar bucket].

lag(1),

[date of posting].[fiscal month].

lag(1)

) +

 

( [arbalance],

[ar bucket dim].[ar bucket].

lag(2),

[date of posting].[fiscal month].

lag(2)

)

but have keep going long time.  there more efficient way?  column header number example shows - cycle through values somehow in lag() function?  like:

( [arbalance],

[ar bucket dim].[ar bucket].lag([ar bucket dim].[ar bucket].??),

[date of posting].[fiscal month].

lag([ar bucket dim].[ar bucket].??)

)

 

thank help. 

you use recursive measure.

with member [measures].[test]   	[measures].[arbalance] + ( [measures].[test], [ar bucket dim].[ar bucket].lag(1), [date of posting].[fiscal month].lag(1) )

 

 

mosha had excellent blog post on few years that's still applicable.

http://sqlblog.com/blogs/mosha/archive/2005/01/23/recursion-in-mdx-how-deep-can-it-go.aspx

hth, martin

SQL Server  >  SQL Server Analysis Services