Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
escaper-
Contributor II
Contributor II

Moving average in a line chart with RangeAVG

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.

screenshot line chart rangeavg.png

 

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

screenshot line chart rangeavg settings.png

Labels (6)
7 Replies
QlikTom
Employee
Employee

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

qliktom_0-1593200529759.png

 

escaper-
Contributor II
Contributor II
Author

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?

QlikTom
Employee
Employee

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

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

sunny_talwar

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

or

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

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.

sunny_talwar

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

onnoemergo
Partner
Partner

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.