1 Reply Latest reply: Jul 9, 2014 11:40 AM by Gysbert Wassenaar RSS

    Rolling averages question

    Matt Maino

      Hi everyone,

       

      I am interested in creating a chart that contains 3 month rolling averages for my data. What I am trying to do for each data point is calculate an average of the current dimension value and the 2 dimension values previous - and plot it as one data point for the current dimension value. (And repeat this for every dimension value.) The dimension that I am using is called MonthsSinceOrig. I am currently working with set analysis and I cant seem to figure out how to get the values for the previous 2 dimension values. Shown below is what I have so far. I've included (in red) pseudo code into the set analysis so that you will be able to see what I am trying to do:

       

      avg(

      sum({$<[Loan Type]={'1'}, [Note Year] = {2008}>} Balance) / sum({$<Stage=, [Loan Type]={'1'}, [Note Year] = {2008}>} Balance)

      + sum({$<[Loan Type]={1'}, [Note Year] = {2008}, MonthsSinceOrig = current dimension value -1>} Balance) / sum({$<Stage=, [Loan Type]={'1'}, [Note Year] = {2008, MonthsSinceOrig = current dimension value -1}>} Balance)

      + sum({$<[Loan Type]={1'}, [Note Year] = {2008}, MonthsSinceOrig = current dimension value -2>} Balance) / sum({$<Stage=, [Loan Type]={'1'}, [Note Year] = {2008}, MonthsSinceOrig = current dimension value -2>} Balance))

       

      I have a feeling that this is not possible, but I figured that I would ask anyway. If it is not, does anybody know of another way to accomplish this? Thanks.