topic Re: Moving average in a line chart with RangeAVG in App Development

Moving average in a line chart with RangeAVG

escaper- — Sat, 16 Nov 2024 18:26:32 GMT

Hi fellow developers,

I'm trying to show several measures in a single line chart.

For 3 categories I want to show the moving average of the past three weeks.

This works in a pivot table when I put the categories in the columns and this formula in the measure:

RangeAvg(
Below(
Sum( {$ <Merk={'BrandX'}, Antwoord={'1'} >} Weight)/Sum({$ <Merk={'BrandX'}, Antwoord-={'Niet beantwoord'} >} Weight)
, 0,3))

However, this does not work in the line chart. I think this has to do because of the categories.

That is why I tried using an AGGR function just like here https://community.qlik.com/t5/Qlik-Sense-App-Development/Rangesum-Above-in-Line-Chart/td-p/1645489.

These numbers are not correct unfortunately in the pivot table - it also does not work in the line chart.

Aggr(
RangeAvg(
Below(
Sum( {$ <Merk={'BrandX'}, Antwoord={'1'} >} Weight)/Sum({$ <Merk={'BrandX'}, Antwoord-={'Niet beantwoord'} >} Weight)
, 0,3))
, Vraag, WeekYear)

I tried playing with above/below and week/weekyear but that does not help.

Does anyone know how to fix this? Is my syntax wrong? I would like to prevent myself adding a seperate measure for every category because I would have to do it for a lot charts.

Kind regards,

Michael

Here is an example of what I want to do with the line chart, and the scores in the pivot table.

Here are my line chart settings. My categories are in 'Statements' and I've used a if statement to pick a few categories.

Re: Moving average in a line chart with RangeAVG

QlikTom — Fri, 26 Jun 2020 19:44:53 GMT

What version of Qlik Sense are you running?

As of February 2020 release, modifiers like Moving Averages are configurable via the UI for Table, Bar, Combo, and Line Charts.
https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Measures/modifiers.htm

Re: Moving average in a line chart with RangeAVG

escaper- — Fri, 26 Jun 2020 20:09:53 GMT

We're on nov 2019.

The page you shared shows an interesting picture of multiple moving averages. I will definitely explore the option to upgrade the server.

Does this mean it wasn't possible before?

Re: Moving average in a line chart with RangeAVG

QlikTom — Fri, 26 Jun 2020 20:47:08 GMT

It was possible, but required you to author your own expressions.

As you can see, selecting Moving Average from a dropdown menu and choosing in how many steps back you want to go is a little easier. I just wanted to confirm you were doing it the hard way if you didn't have to.

I believe there are several posts on the community about doing this this via expression:
This one looks promising, it is QlikView, but the expression syntax is the same.
https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

P.S. a lot more than just moving averages have been added since Nov 2019, you can keep an eye on the new stuff here:
https://demos.qlik.com/qliksense/WhatsNew

Re: Moving average in a line chart with RangeAVG

sunny_talwar — Tue, 30 Jun 2020 18:26:28 GMT

Can you try this Michael -

Aggr( RangeAvg( Below( Sum( {$ <Merk={'BrandX'}, Antwoord={'1'} >} Weight)/Sum({$ <Merk={'BrandX'}, Antwoord-={'Niet beantwoord'} >} Weight) , 0,3)) , Vraag, (WeekYear, (NUMERIC)))

Aggr( RangeAvg( Above( Sum( {$ <Merk={'BrandX'}, Antwoord={'1'} >} Weight)/Sum({$ <Merk={'BrandX'}, Antwoord-={'Niet beantwoord'} >} Weight) , 0,3)) , Vraag, (WeekYear, (NUMERIC)))

Re: Moving average in a line chart with RangeAVG

escaper- — Fri, 10 Jul 2020 12:32:41 GMT

Hi,

Thank you for replying and taking a look. What does adding the NUMERIC part do?

Unfortunately it did not affect the result. It gives me the same line as

Aggr(
RangeAvg(
Below(
Sum( {$ <Merk={'BrandX'}, Antwoord={'1'} >} Weight)/Sum({$ <Merk={'BrandX'}, Antwoord-={'Niet beantwoord'} >} Weight)
, 0,3))
, Vraag, WeekYear)

I made a workaround by only showing one line in the line chart and the server update will be planned later to try the new feature.

Re: Moving average in a line chart with RangeAVG

sunny_talwar — Fri, 10 Jul 2020 15:08:18 GMT

You can read about NUMERIC part here -> The sortable Aggr function is finally here

Re: Moving average in a line chart with RangeAVG

onnoemergo — Fri, 31 Jul 2020 12:56:44 GMT

As this challenge is easier solved in a more recent version of Qlik Sense (as has been posted), I suggest we put upgrading on my ToDo list. I will be back on your site next week. It should be fine to kick the version to April 2020 SP5 without much trouble.