Search code examples
mdxpentahomondrian

How to calculate recent row average value using MDX?

My data example:

id  value_a 
1   1.0
2   2.0
3   3.0
4   4.0

which I want is

id / value_a / recent_n_avg

1    1.0     0.33

2    2.0     1.00

3    3.0     2.00

4    4.0     3.00

recent_n_avg is average for recent n rows( n=3 in example).

How to use MDX to solve this problem.

Thanks.

Solution

  • If you want do it only for one dimension and this dimension is flat you can write following expression for new calculated member:

    SUM({[Dimension].CurrentMember.Lag(2):[Dimension].CurrentMember}, [Measures].[Your measure])/3

    Also, you should remember about member "All" and members # 1,2 in your dimension.