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.
Set analysis calculates one set per chart, not a set per dimension value. That means you can't use set analysis to do what you want. See this document for more information: Calculating rolling n-period totals, averages or other aggregations