topic rangeAvg excluding the first few datapoints in QlikView

rangeAvg excluding the first few datapoints

Mon, 11 Jun 2012 09:18:11 GMT

I am trying to calculate a 6 month moving average. I have read fairly extensively and tried several options, and what I have so far is this:

RangeAvg(above(sum({1} [AllShareValue]),0,6))

The reason why I use the {1} set analysis is because when people select a section of months, the first selection needs to go 6 months back out of that selection period in order to calculate itself, so I have to use the {1} so that the calculation knows there are more months behind the first selected one.

This is all well and good, but the problem is that the graph is showing data outside of the selection now.

I want to calculate using data outside of the selection, but then display data within the currently selected dates

I'm fairly new to qlikview, and I'm struggling to rap my head around this one. Any ideas?

Please don't reply with qlikview documents containing the calculation, because I currently don't have the full version of Qlikview. I'm creating a prototype to illustrate to my boss what qlikview can do, in order to convince him to buy it.

rangeAvg excluding the first few datapoints

Mon, 11 Jun 2012 09:25:57 GMT

I would just like to add that my question is the same as this guy's (or girl's):

http://community.qlik.com/message/120732#120732

Re: rangeAvg excluding the first few datapoints

swuehl — Mon, 11 Jun 2012 09:43:31 GMT

You can try embedding your expression in an advanced aggregation, like

=aggr( RangeAvg(above(sum({1} [AllShareValue]),0,6)) , Period)

There is one pitfall here, aggr() function uses load order as sort order for its dimension valuesm, so you must take care that your Period values are sorted correctly while loading in.

rangeAvg excluding the first few datapoints

Mon, 11 Jun 2012 09:55:51 GMT

Thanks. I really didn't think it would be so easy.

I had a look at the aggr function but I guess I didn't understand it enough because I couldn't see how it could help.

rangeAvg excluding the first few datapoints

swuehl — Mon, 11 Jun 2012 09:59:45 GMT

Yes, that's probably not clear when looking at the functions description.

The aggr() allows us to have a full embedded table of Period values (due to the {1} ) for the Rangeavg(above()) calculation, but then the results shown (i.e. calculated values per dimension value in the the chart object) are limited to the current selection.