Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Rolling averages question

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.

1 Reply
Gysbert_Wassenaar
Not applicable

Re: Rolling averages question

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


talk is cheap, supply exceeds demand