topic Rolling Linest_m function for time series analysis in App Development

Rolling Linest_m function for time series analysis

Loghry14 — Sat, 16 Nov 2024 18:11:04 GMT

Hello new here. I am running into an issue with a unique ask. I am trying to get a table that has a rolling linest_m for the past n days (lets say 5 for now) to help with smoothing a graph. Right now I have:

linest_m(aggr((sum([38_1_1])+sum([38_2_1])+sum([38_3_1]))/(sum([35_1_1])+sum([35_2_1])+sum([35_3_1])), YYYYMMDD), YYYYMMDD)

The term (sum([38_1_1])+sum([38_2_1])+sum([38_3_1]))/(sum([35_1_1])+sum([35_2_1])+sum([35_3_1]) is one of our metrics. This code works if I change the datefield on the table to a weekly or monthly basis; however, it only gives the regression for that week or month. I am looking to have a daily regression that includes that day and the previous n days. I have tried using the above function to no avail (could be using it incorrectly).

Ultimately looking to plot this in a graph. Thanks for your help.

Re: Rolling Linest_m function for time series analysis

sunny_talwar — Wed, 26 Aug 2020 19:48:43 GMT

Would you be able to provide a sample and the output you expect to see from it?

Re: Rolling Linest_m function for time series analysis

Loghry14 — Wed, 26 Aug 2020 20:31:08 GMT

Hey Sunny. I am attaching an excel file. It is possible to do this in excel, but I like the ability to have the graphs made each day in Qlik Sense for others in our business to use for the time series. The data is on the far left and I have done 3 different rolling linear regression series (one where n=5, n=10, and n=17) You can see the graphs start to get smoother and that is what we want. I can get the regression model in qlik sense from above; however, I can't get it to do a linear model on every 5 data points like I did in excel. So what I am looking for is to get essentially what I have done in excel to run in Qlik sense if possible. Thanks