Rolling averages question

Wed, 09 Jul 2014 15:35:13 GMT

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.

Re: Rolling averages question

Gysbert_Wassenaar — Wed, 09 Jul 2014 15:40:05 GMT

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

topic Rolling averages question in QlikView

Rolling averages question

Re: Rolling averages question