topic Re: Calculating moving average/date order issue in QlikView

Calculating moving average/date order issue

tomelmslie — Sat, 16 Nov 2024 04:45:44 GMT

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

Re: Calculating moving average/date order issue

sunny_talwar — Mon, 14 Jan 2019 17:28:44 GMT

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

Re: Calculating moving average/date order issue

tomelmslie — Mon, 14 Jan 2019 17:49:15 GMT

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

Re: Calculating moving average/date order issue

sunny_talwar — Mon, 14 Jan 2019 17:53:13 GMT

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

Re: Calculating moving average/date order issue

tomelmslie — Mon, 14 Jan 2019 18:00:42 GMT

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?

Re: Calculating moving average/date order issue

sunny_talwar — Mon, 14 Jan 2019 18:06:52 GMT

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.

Re: Calculating moving average/date order issue

tomelmslie — Tue, 15 Jan 2019 10:10:41 GMT

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

Re: Calculating moving average/date order issue

sunny_talwar — Tue, 15 Jan 2019 11:56:16 GMT

Good to know 🙂