Hi Alec,

Currently, the CurrentMember() function does not actually return the name of the current member, so the range approach will not work. We hope to add this functionality in a future version of Pyramid.

Meanwhile, you can achieve what you want by using multiple instances of the Lag() function in your calculation. Here's an example of a 3 month rolling average. the first Data Point defines the measure, sales and the current member of the time period concerned, in this case months. Subsequent DataPoints use the Lag() function on the months, incrementing the lag by one each time, so that the last DataPoint is, in this instance Lag(2). We add these together, then divide by 3:

However, the first and second values for my 3 month moving average are low because there are only 1 and 2 time periods available respectively and my average is always dividing by 3. This may be what you want, but usually in Moving Average calculations, if there are insufficient elements available in the SUM for the  denominator, the average is zeroed or nulled.  

We can achieve this by adding an IF statement, testing for nulls for Sales in the LAG(2) and LAG(1) time periods:

This works fine for my 3 month moving average, but nesting IF statements for a 12 month moving average would be very clumsy.

PQL offers a CASE statement that can be used to test multiple conditions and assign values to each outcome:

          Case(criteria(criteria set), criteriaResults(outcome set), default value)  

Using this instead of the IF statement looks like this. Note we have moved into the Script mode of the Formulate editor, as it is somewhat easier to use when creating more complex functions than the graphical editor. the result is identical to the above.

CASE(Criteria(
  ([measures].[Data Sales],Lag([Data].[Date full month name],2)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],1)) = 'null'
 ),   
   CriteriaResults(
         'null',
         'null'),

(([measures].[Data Sales],CurrentMember([Data].[Date full month name]))+([measures].[Data Sales],Lag([Data].[Date full month name],1))+([measures].[Data Sales],Lag([Data].[Date full month name],2)))/3

)

Here's an example of a 13 month rolling average using the CASE statement.

CASE(Criteria(
  ([measures].[Data Sales],Lag([Data].[Date full month name],12)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],11)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],10)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],9)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],8)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],7)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],6)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],5)) = 'null',       
  ([measures].[Data Sales],Lag([Data].[Date full month name],4)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],3)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],2)) = 'null',
  ([measures].[Data Sales],Lag([Data].[Date full month name],1)) = 'null'
 ),   
   CriteriaResults(
         'null',
         'null',
         'null',
         'null',
         'null',
         'null',
         'null',
         'null',
         'null',
         'null',
         'null',
         'null'),

(([measures].[Data Sales],CurrentMember([Data].[Date full month name]))+([measures].[Data Sales],Lag([Data].[Date full month name],1))+([measures].[Data Sales],Lag([Data].[Date full month name],2))+([measures].[Data Sales],Lag([Data].[Date full month name],3))+([measures].[Data Sales],Lag([Data].[Date full month name],4))+([measures].[Data Sales],Lag([Data].[Date full month name],5))+([measures].[Data Sales],Lag([Data].[Date full month name],6))+([measures].[Data Sales],Lag([Data].[Date full month name],7))+([measures].[Data Sales],Lag([Data].[Date full month name],8))+([measures].[Data Sales],Lag([Data].[Date full month name],9))+([measures].[Data Sales],Lag([Data].[Date full month name],10))+([measures].[Data Sales],Lag([Data].[Date full month name],11))+([measures].[Data Sales],Lag([Data].[Date full month name],12)))/13

)

Hope that helps.

Ian 

Ah!

It occurred to me after I had written the above, that in fact, we only need to test the Sales value for null for the first element in the moving average. I.e. if we are creating a 3 month MA, then only need to test for the LAG(2) time period, as by implication, if that is null, so will all the other preceding periods.

This simplifies things enormously, so we can easily create the 13 month moving average with one IF test:

Still, at least you've had a tutorial on using the CASE statement, which I hope will come in handy in the future! :-)

Ian