8 Replies Latest reply: May 30, 2011 11:08 AM by Yonatan landau

# Moving Average - what to do w the first 20 days?

I've successfully added a 20-day moving average line to my chart; however the first twenty days are calculating the average based upon only the selected data so they're not a true mvg avg (meaning Day 1's mvg avg= D1 sales, D2 mvg avg= D1+D2 sales/2, etc).

The data I need to do the calcuation is loaded...I just can't figure out the set analysis (or any other solution) needed to base the mvg avg calculation on days that are excluded from the current selection.

• ###### Moving Average - what to do w the first 20 days?

How did you do the moving average? With the accumulation option in the expression tab or a rangesum(above()) function?

I can't think of any pretty and easy solution for this one since set expression doesn't work well when you want to define the set of the dimension you are using in your chart and the above doesn't seem to see rows that are not in the table.

However if you can create a date island table that contains the dimension of the chart and is not connected in the data model with the fields in your expression you will be able to do what you are looking for although the time to generate the chart greatly increases with large data volumes.

An example is attached.

Regards.

• ###### Moving Average - what to do w the first 20 days?

If I've understood the question, generate a table like this:

AsOfDate, Date
Jan 20, Jan 20
Jan 20, Jan 19
Jan 20, Jan 18
...
Jan 20, Jan 1
Jan 21, Jan 21
Jan 21, Jan 20
...
Jan 21, Jan 2
etc.

Use AsOfDate as your dimension and what you make your selections on. Use avg(Whatever) as your expression, no accumulation. I expect you'll get a 20 day moving average, and even the first selected day will be the average of the past 20 days (as long as you have at least 20 days of actual data before that point). Do a search for "AsOf" to find script and examples.

• ###### Moving Average - what to do w the first 20 days?

I ended up using a slider/calendar object to set the Static Min & Static Max values on the Dimension Axis. I created 2 variables (vMaxAxis & vMinAxis) then used the slider to set the values.

This allowed the 20 day average to be calculated on the data that isn't visible.

• ###### Re: Moving Average - what to do w the first 20 days?

I did the excat same procedure and it still didn't take in a count unvisible months...

(I'm looking for 3 months moving average)

I got a really serious headache...

I'll be very greatful for some good advice!!!

Thanks!!!

• ###### Re: Moving Average - what to do w the first 20 days?

The attached qlik is an example.

• ###### Re: Moving Average - what to do w the first 20 days?

Hi,

thank u for your quick replay...

can you please attach print screen of the file you sent?

I don't have qlikview liscense yet, so I can't open the file...

Again, thanks allot!!!

• ###### Re: Moving Average - what to do w the first 20 days?

screenshot is attached

• ###### Re: Moving Average - what to do w the first 20 days?

thank u so much for your efforts and your willingness to help!!!

I'm sorry to be so problematic, but after doing all the steps in the process in the screenshot, I eventually got a graph with x axis between the 2000 to 2020, even though I inserted as a dimention "monthStart" for dates which begin in 2009 and end in 2011...