Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
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
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)))
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.
You can read about NUMERIC part here -> The sortable Aggr function is finally here
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.