topic Re: How to create a moving average line chart in QlikSense in App Development

How to create a moving average line chart in QlikSense

wenjun2016 — Thu, 29 Dec 2016 04:35:17 GMT

Hi,

I am beginner to QlikSense.

I met this problem recently.

Raw dataset: 10 locations, 5 KPI indicators per location. (same set of 7 indicators)

I computed average for 5 KPI per location. Now there is one average per location.

Then, I did a total to display in the table Total Average for all 10 locations.

All the above computed data are not store in a new table, they are computed using measure with formula and displayed directly,

Now, my requirement is to plot a line chart of the total average over a period of time (can be 1 month, 1 year, 2 year etc...) for "selected/filtered location & selected/filtered KPI"

e.g. yearly moving average trending:

Year 2013 Sept to Year 2014 Aug -> Total Average :
Year 2013 Oct to Year 2014 Sept -> Total Average :
Year 2013 Nov to Year 2014 Oct -> Total Average :
Year 2013 Dec to Year 2014 Nov -> Total Average :

Use these 4 Total averages to plot as 4 dots on the Line chart.

I saw QlikView can do moving arrange using Rangave()

Can it work for QlikSense, and in my case, the Total Average is based on a computed averages (must I store them in one new column first?)

Re: How to create a moving average line chart in QlikSense

sunny_talwar — Thu, 29 Dec 2016 12:54:35 GMT

Would you be able to provide raw data and expected output from that raw data to help you better?

Re: How to create a moving average line chart in QlikSense

wenjun2016 — Fri, 30 Dec 2016 09:21:59 GMT

Hi Sunny,

Thanks for your reply.

Give me sometime to mask my data and will upload together with my dashboard application.

Happy New Year!

Re: How to create a moving average line chart in QlikSense

wenjun2016 — Tue, 03 Jan 2017 10:07:34 GMT

Hi Sunny,

Please see my dataset attached.

The summary tab is the computed averages for checking purpose.

Please also check the application attached. I am able to achieve the line chart by location, by month, by year.

However, I also need to have a line chart that is presenting moving averages, like every one point is a total average of a selected period (e.g. 6 months: Sept 14 - Feb 15, Oct 14 - Mar 15, Nov 14 - Apr 15, Dec 14 - May 15 ...)

Feel free to contact me if you are unclear about the requirement.

Thank you.

Wenjun

Re: How to create a moving average line chart in QlikSense

DKK — Tue, 18 Jul 2017 13:17:17 GMT

Hi Wenjun,

Did you have a solution for the above requirement ?. I am also looking for the same kind of result.

Regards

Kiran