Calculating moving average/date order issue

[tomelmslie]

Hi,

I am trying to calculate a moving average for a data set with no luck.

I have tried to use the rangeavg(above(sum(sales),0,18)) code, but have encountered an issue with the date order.  It appears to be ordering by the value of the measure rather than by the date order (dimension). This seems to be preventing me from calculating an average (sales value returns zero).

I believe an AGGR function might be able to help here, but not sure exactly how to use it. Is there a way I can wrap the rangeavg formula with an AGGR to make this work? Many Thanks!

This is the data I am working with:

Sales	Date
67	2017-06
65	2017-09
62	2017-10
59	2017-08
58	2017-11
57	2017-05
57	2017-07
52	2017-12
48	2018-01
47	2018-02
42	2018-03
35	2018-06
35	2018-07
32	2018-05
31	2018-04
23	2017-02
23	2017-04
16	2017-03

[sunny_talwar]

If your date is sorted in the script... you can do this

Aggr(RangeAvg(Above(Sum(sales), 0, 18)), Date)

If it isn't, but you have QV12 or above, you can use this

Aggr(RangeAvg(Above(Sum(sales), 0, 18)), (Date, (Numeric)))

If your date isn't sorted and you are still using QV11.2 or lower, you will need to make sure that your Date field is sorted in ascending order in the script and then you can use the first expression

[tomelmslie]

Hi Sunny,

Thanks for your help.

I tried both options but no luck - strangely my date order appears correctly now, but the sales column returns zero value for each date point. Might I have a problem with the sales measure in this case?

I am using Qlik Sense 2018 - not sure if this helps...

Tom

[sunny_talwar]

---

@tomelmslie wrote:

I am using Qlik Sense 2018 - not sure if this helps...

---

You posted your question in QlikView section of the community which is what confused me. But if you are using Qlik Sense 2018, you should def. be able to use this

Aggr(RangeAvg(Above(Sum(sales), 0, 18)), (Date, (Numeric)))

Few things to check if this became 0. Is your Date field called Date or something else? Make sure to use the correct name. Also, does this happen when you make a selection or does it no work with or without selections?

[tomelmslie]

Apologies for that! The field name is definitely correct. I have tried this in a table as well as different graphs, and each time zero is returned. I have tested the code with other measures other than sales, and it works - I suspect it is an issue with the construction of measure in that case?

[sunny_talwar]

I am not sure why you think sales is an issue, but if this worked

RangeAvg(Above(Sum(sales), 0, 18))

I don't see why you think sales is causing the problem.

But having said that, I don't really have a way to test unless you are able to share your app for me to look at.

[tomelmslie]

I played around with the formula and RangeAvg(Above(Count(Sales), 0, 18)) seems to have done the trick which is strange!

[sunny_talwar]

Good to know 🙂