How to build a small history
Often it is important to not only work with single real-time data points in the moment, but to build a history and work with a series of values.
In this post I will show three different ways of creating short data histories.
To access and work with larger series of data connecting to and using a database is usually the best approach.
In the following example the Inbox receives messages with simulated data as payload. This data will be used to create two kinds of histories (based on steps and based on time).
Versions 1 and 2 result in a data history based on calculation steps whereas Version 3 creates a data based on time.
Download my exported Stream Machine to see what I did in detail.
Here we create a history leveraging a central characteristic of Streamsheets.
Every sheet is calculated from left to right and from top to bottom. Consequently, cell A1 is calculated first and then B1, C1 etc. before the next rows follow. Thanks to this concept, we can use references to achieve our goal.
The cells at the very bottom (here B20 and C20) always reference to the data input (here B7 and B6) and the cells above reference to the cells below. As higher cells are calculated first, they fetch the values of lower cells before these are calculated. In the end the last cells take the latest data input. Effectively it looks as if new values enter the history at the bottom and move up before leaving the series at the top.
This screenshot shows the used formulas to build a history using references and the sheet´s calculation order (you can get this view in the Streamsheet settings under the cogwheel in the top right corner of your sheet)
In this version we use a STACKADD() function to create a table of a certain size. To understand the stack functionality you can have a look here.
STACKADD() will add a value with every step to the stack. When the created table is full, it automatically deletes the oldest entry.
To build a history of values which occur within a certain time period, the function TIMEAGGREGATE() is a good option. With this function values are not stored calculation step-by-step but e.g. by default based on when they are calculated in the system.
This function has a lot of applications. If you want, take your time and read about it in our docs.
In the following scenario, we use this function to collect the data of the last 11 seconds, calculate averages in intervals of 1 second and display the results in intervals of 1 second. This will then be displayed in the selected cells (I10:J20).
And this is how it looks when running the machine:
Download Stream Machine:
HistoryExample.json (148.1 KB)