Find the Rolling Averages

[Ali]

Hello, 

I'm trying to build a trend of yearly rolling averages based on an existing trend. 

I know if use signal from condition and then I input the condition and the bounding condition (yearly), it doesn't give me the rolling average for the past year. I need to look at it in a way where if I look at trend and select today's date, it gives me the average for the past year. So basically a trend of yearly rolling averages. Please help, thanks

 

[Teddy]

Ali,

I will preface this with saying that calculating a yearly rolling average can be slow. It requires looking at a lot of data.

You can create the condition you are looking for with the formula tool. The following syntax will generate a condition with a capsule everyday extending 1 year. 

periods(1year,1day)

See screen shot below for formula details.


Lastly you can calculate the rolling average with the signal from condition tool.  See screen shot below for how to set up a yearly rolling average.

Hope this helps,

Teddy
 

 

Content Verified FEB2024

Edited February 26 by Amanda Chng
Adding Content Verified text at the end of the answer

[Thorsten Vogt]

Hi Ali,

you can try using runningAggregate-Formula:

$signal.runningAggregate(average(), years()) caclulates the running average for each year resetting on 01.01. 00:00

Regards,

Thorsten

Edited December 11, 2019 by Thorsten Vogt

[Ali]

Thorsten, 

I used your formula to calculate the rolling avgs of previous year. I got the following trend (Formula2). 

The green trend showing an avg of 0.0044 based on the previous year ending 12/11/2019 at 6:16 pm. However , when I check the same point of the rolling average in Formula 2, it's showing a different number of 0.004. This is case sensitive numbers, no decimals make a difference. I don't know why the rolling average at the same ending point (12/11 6:16pm)  is not the exact same as the initial trend. I even downloaded data to excel to verify the numbers. Thanks

[Thorsten Vogt]

Hi Ali,

I think the green trend is calculating the average from 12/11/2018 6:16 pm to 12/11/2019 6:16 pm. The formula I gave you is resetting at 01/01/2019 and therefore calculating the average from 01/01/2019 00:00 to 12/11/2019 6:16 pm which might cause the difference. To use the formula with the intended range you may change it to

$signal.runningAggregate(average(), condition(1y, capsule(now()-1y, now())))

That will create a condition with one capsule beginning 1 year ago and ending now which will be used for the calculation. The value at the end of the capsule then matches the average calculated by Seeq for the display range.

However, as the window for the calculation (now - 1y) is changing, the calculated values for a specific timestamp are uncertain and may change frequently. Therefore I think you should disable the cache for the calculated item. 

See discussion in following topic: 

 

Regards, 

Thorsten

[Ali]

Thorsten,

Thanks a lot, this worked as intended. I have another question please. If I want to input future data for the green curve and then need to extend the rolling averages curve to predict how the rolling average changes with time and input data. Is it possible to do that in Seeq?

My intention is to input weekly/monthly future predictions data points and then see how the rolling averages for previous year changes with the input data.

Thanks, 

Ali

Edited December 12, 2019 by Ali

[Thorsten Vogt]

Hi Ali,

yes, you can do this. For demo I created a forecast based on the last 2 days:

Then I calculated the running average for the forecast:

 

In the resulting view you can see the average starting and ending two days later:

 

Regards,

Thorsten

 

 

[Ali]

Thank you. What if I want to input the forecast numbers myself (assume wb avg temp =72 F for dec, 71F for Jan, 69 for Feb, etc) rather than using existing data and then calculate the rolling averages for the future. Is it possible?

[Thorsten Vogt]

You can use signal() for this

But I would suggest getting the values from a data source connected to Seeq (like PI or a relational database) rather than filling them in manually.

Regards,

Thorsten