CODEWITHSUNDEEP
conditional-statementsssasmdxlag
SniperPro
SniperPro

Reputation: 105

How to calculate the std of the previous 10 values over the time of a measures on MDX

I have to calculate the standard deviation of the 10 previous value of a measure X over the date hierarchy: Something like that:

+------------+---------+---------+
|    Date    |    X    |  std10d |
+------------+---------+---------+
| 24/04/2019 | 238985  |         |
| 25/04/2019 | 61567   |         |
| 26/04/2019 | -37350  |         |
| 27/04/2019 | 27482   |         |
| 28/04/2019 | 65499   |         |
| 29/04/2019 | 3373    |         |
| 30/04/2019 | 88660   |         |
| 01/05/2019 | 22094   |         |
| 02/05/2019 | 99731   |         |
| 03/05/2019 | -4878   |         |
| 04/05/2019 | -100024 |   77268 |
| 05/05/2019 | -54204  |   60966 |
| 06/05/2019 | -9833   |   63679 |
+------------+---------+---------+

I khow that the MDX formula should be like that : 

stdev
(
[00_Time].[0_dateHierarchy].PrevMember.Lag(9) : 
[00_Temps].[0_dateHierarcy].PrevMember, 
[Measures].[X]
)

But I don't know what condition add to prevent the calculation of the first 10 value of std10d.

Upvotes: 1

Views: 39

Answers (1)

MoazRub
MoazRub

Reputation: 2911

The expression [00_Time].[0_dateHierarchy].PrevMember.Lag(9).name for first 9 members will return null.Just check that null

Case 
[Product].[Subcategory].currentmember.lag(9).name =null
then 
null
else 
stdev
(
[00_Time].[0_dateHierarchy].PrevMember.Lag(9) : 
[00_Temps].[0_dateHierarcy].PrevMember, 
[Measures].[X]
)
end

Upvotes: 1

Related Questions