topic Re: RangeAvg with Above/Below getting strange results in QlikView

RangeAvg with Above/Below getting strange results

Thu, 10 Oct 2013 17:26:55 GMT

UPDATE per discussion below: The functions are behaving as advertised for Above() and Below(). However the examples used in the HELP docs for the various Range functions are incorrect when using negative numbers with Above(). Apparently I'm not the first person to encounter this issue. The Help Docs for Range functions need to be corrected.

Hi all,

I'm trying to calculate a 7-day moving average for a specified Cost. In this case, I actually want to take the average of the current day and three previous days and three subsequent days. Reading up on RangeAvg, this seemed rather trivial, but I am not achieving the expected results.

So to calculate the moving average, I would just need to do either of the following:

=rangeavg(below(sum(totalcost),-3,7)) ------- This should take the RangeAvg of the totalcost starting at 3 above and include through 3 below the current row. What I actually see is the average of the 7 rows below starting 9 rows above.

The other option is to use the above function:

=rangeavg(above(sum(totalcost),-3,7)) ------- This should take the RangeAvg of the totalcost starting at 3 below and include through 3 above the current row. What I actually see is the average of the 7 rows above starting 9 rows below.

I tried a couple other combinations of numbers to get the rows right, but nothing seemed to work. I'm pretty sure I'm missing something fundamental here. Please help

Data Set:

LOAD * INLINE [
Date, totalcost, Expected
01/01/2013, $30.87,
01/02/2013, $78.66,
01/03/2013, $71.44,
01/04/2013, $42.00, $53.40
01/05/2013, $37.44, $55.33
01/06/2013, $56.24, $49.06
01/07/2013, $57.12, $41.99
01/08/2013, $44.38, $38.12
01/09/2013, $34.80, $36.22
01/10/2013, $21.92, $32.25
01/11/2013, $14.95, $29.37
01/12/2013, $24.12, $26.84
01/13/2013, $28.44, $28.24
01/14/2013, $37.00, $32.63
01/15/2013, $26.64, $39.73
01/16/2013, $44.59, $41.01
01/17/2013, $52.70, $43.68
01/18/2013, $64.60, $48.48
01/19/2013, $33.12, $59.52
01/20/2013, $47.09, $62.00
01/21/2013, $70.62, $58.36
01/22/2013, $103.95, $56.89
01/23/2013, $61.94, $60.85
01/24/2013, $27.18, $63.61
01/25/2013, $54.34, $61.85
];

Output:

Date	totalcost	Expected average	=rangeavg(below( sum(totalcost),-3,7))	=rangeavg(above( sum(totalcost),-3,7))
01/01/2013	$30.87		-	$41.99
01/02/2013	$78.66		-	$38.12
01/03/2013	$71.44		-	$36.22
01/04/2013	$42.00	$53.40	$30.87	$32.25
01/05/2013	$37.44	$55.33	$54.77	$29.37
01/06/2013	$56.24	$49.06	$60.32	$26.84
01/07/2013	$57.12	$41.99	$55.74	$28.24
01/08/2013	$44.38	$38.12	$52.08	$32.63
01/09/2013	$34.80	$36.22	$52.78	$39.73
01/10/2013	$21.92	$32.25	$53.40	$41.01
01/11/2013	$14.95	$29.37	$55.33	$43.68
01/12/2013	$24.12	$26.84	$49.06	$48.48
01/13/2013	$28.44	$28.24	$41.99	$59.52
01/14/2013	$37.00	$32.63	$38.12	$62.00
01/15/2013	$26.64	$39.73	$36.22	$58.36
01/16/2013	$44.59	$41.01	$32.25	$56.89
01/17/2013	$52.70	$43.68	$29.37	$60.85
01/18/2013	$64.60	$48.48	$26.84	$63.61
01/19/2013	$33.12	$59.52	$28.24	$61.85
01/20/2013	$47.09	$62.00	$32.63	$47.82
01/21/2013	$70.62	$58.36	$39.73	$40.76
01/22/2013	$103.95	$56.89	$41.01	$54.34
01/23/2013	$61.94	$60.85	$43.68	-
01/24/2013	$27.18	$63.61	$48.48	-
01/25/2013	$54.34	$61.85	$59.52	-

Re: RangeAvg with Above/Below getting strange results

swuehl — Thu, 10 Oct 2013 17:42:41 GMT

Try

RangeAvg(above(sum(totalcost),1,3),below(sum(totalcost),1,3),sum(totalcost))

edit: a similar discussion

Re: RangeAvg with Above/Below getting strange results

felipe_dutra — Thu, 10 Oct 2013 20:02:43 GMT

Hi!

See this example:

Re: RangeAvg with Above/Below getting strange results

Fri, 11 Oct 2013 14:13:26 GMT

Thanks swuehl and Felipe Dutra! Both answers are very helpful and provide a workaround for the solution. I am also curious, however, why the function does not behave as expected in my example. Is it a QlikView bug?

Re: RangeAvg with Above/Below getting strange results

swuehl — Fri, 11 Oct 2013 14:20:47 GMT

It depends on where your expectation comes from.

If I look into my HELP file for above() function, it says:

"Specifying an offset greater than 1 lets you move the evaluation of expression to rows further up the current row. A negative offset number will actually make the above function equivalent to a below function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current row. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table rows counting upwards from the original cell. In this form, the function can be used as an argument to any of the special Chart Range Functions. "

=rangeavg(above(sum(totalcost),-3,7))

should be equivalent to

=rangeavg(below(sum(totalcost),3,7))

and I think (without rechecking your results) that's what you get.

Re: RangeAvg with Above/Below getting strange results

Fri, 11 Oct 2013 18:17:23 GMT

Yup, I read the docs on Above(), Below(), and RangeAve(). It appears that the formula is working properly per the description of Above() and Below(), but the examples in the docs are incorrect for Range____(). For example, from the HELP docs:

rangecount (above(sum(x),-1,3))

returns the number of values within the three results of the sum(x) function evaluated on the row below the current row, the current row and the row above the current row.

rangemin (above(sum(x),-1,3))

returns the lowest of the three results of the sum(x) function evaluated on the row below the current row, the current row and the row above the current row.

The descriptions are incorrect, and I just verified that they do not do what they describe. So, I guess if I just focused on the description of Above() and Below(), and ignored the examples for Range____(), I would not have not had this issue. Lesson learned