Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET:
**REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- RangeAvg with Above/Below getting strange results

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

Not applicable

2013-10-10
01:26 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

RangeAvg with Above/Below getting strange results

*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

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

];

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

1,520 Views

5 Replies

swuehl

MVP

2013-10-10
01:42 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try

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

edit: a similar discussion

felipe_dutra

Partner - Creator

2013-10-10
04:02 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi!

See this example:

Not applicable

2013-10-11
10:13 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

1,168 Views

swuehl

MVP

2013-10-11
10:20 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

So

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

1,168 Views

Not applicable

2013-10-11
02:17 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,168 Views