Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

8 Replies
pover
Luminary Alumni
Luminary Alumni

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.

johnw
Champion III
Champion III

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.

Not applicable
Author

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.

Not applicable
Author

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!!!

Not applicable
Author

The attached qlik is an example.

Not applicable
Author

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!!!

Not applicable
Author

screenshot is attached

Not applicable
Author

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...