Skip to main content
Version: Streamsheets 3.0

Create data history

Often it is essential to not only work with single real-time data points but to build a history and work with a series of values. Besides using databases, you can store a finite amount of information within your running application.

Using References​

A straightforward way to create not only a data history but a full-blown data table, which updates over time, can be done by understanding the core principle of the Streamsheet calculation process. Every sheet is calculated from left to right and from top to bottom. Consequently, cell A1 is calculated first, then B1, C1, etc., before the next rows follow. This concept allows us to use references to create a data history. Create a table and reference the bottom of the table to the incoming data. Now add in every cell above one cell down facing references. Since a cell above another cell is calculated before the other, the reference is one step in the past. This way, the table will slowly but surely fill up with information.

Using Stack​

The same result can be achieved by using the function STACKADD().

STACKADD() will add value with every step to the stack. When the created table is complete, it automatically deletes the oldest entry. Other stack functions could also help to keep your table up to date. (STACKDROP(), STACKFIND(), STACKUPSERT(), STACKSORT() & STACKROTATE()))

stackadd

Using Table Update​

A powerful function is the TABLE.UPDATE() function. It combines the general possibility of creating a simple table filled with historical data while adding further aggregation functionalities.

Define a cell range to set your table size and define headers for each column in the first row of the table. Dynamic Row and Column Indices make it possible to decide where a value is updated in the table. As the function says, it is only updating an existing table by default. To create more rows or columns, use the parameters "PushRowat" and "PushColumnAt." For example:

=TABLE.UPDATE(A1:D30,1,NOW(),"time",-1,0)

This function would lead to the creation of an entry of the function NOW() in the column for "time". If no such header is given, there won't be a creation. PushRowat = -1 allows the creation of the new row, but PushColumnat = 0 does not allow the creation of a new column.