topic Re: Avg or RangeAvg in App Development

Avg or RangeAvg

Anonymous — Mon, 08 Oct 2018 20:16:43 GMT

Not sure which is the best to use, have tried both, but have not been able to correctly create expression. I want to create a measure that will find average of last 30 days sales. This measure will be used in a table, so when the measure is used it should show different values each day, since the 30 days shift daily. Any help to create this expression? I think it should be one of either avg or rangeavg, but whatever will calculate this value will work. Thank you in advance for help.

Re: Avg or RangeAvg

vamsee — Mon, 08 Oct 2018 20:53:27 GMT

Try

Avg({<Date = {">=$(=Date(Max(Date))-30)))"}>}Sales)

Re: Avg or RangeAvg

dplr-rn — Mon, 08 Oct 2018 22:22:33 GMT

check this post

Calculating rolling n-period totals, averages o... | Qlik Community

Re: Avg or RangeAvg

Anonymous — Tue, 09 Oct 2018 15:41:17 GMT

Avg({<Date = {">=$(=Date(Max([completed_date.autoCalendar.Date]))-30)))"}>}order_total)

Updated formula a little, but do not get correct value, closer though. This expression seems to provide a per order average for the day. I would like the average of total sales for day for days within last 30 days. Tried altering it to be a bit more complex as well, but expression was not calculating anything. Any edit to this expression that may help?

Re: Avg or RangeAvg

dplr-rn — Tue, 09 Oct 2018 15:44:52 GMT

Is date a dimension in your chart? if so it's not possible here to use Date both as a dimension and in the set modifier in the expression.

you may need an as of table or a complex aggr.