Use of Indirect for Time Series Creation

[JRDavisUF]

My system is receiving data at 1s intervals by MQTT. Packed within the JSON message is an array of 10 elements sampled at 0.1s. To make extraction of the data easier, I’m using the indirect function. With the data extracted, I’m trying to use the fact that things are procesed left to right and top down to build a time series (for example, 3 messages in total comprising 30 observations over 3s). When I do this when accessing the data directly, it works as expected, however, if I try to build the time series by accessing the indirect function ( I have the indirect pointing to the data at the bottom of the sheet and then use “=” pointing to the indirects in some cells above) , I get the same values…ie no time series creation.

I’m thinking maybe I need to use something other than the simple “=” way of accessing the data found by the indirect. As such, I tried copyvalues, but rather than returning the values, it returns the cell location (e.g. B134) instead of the value of the cell location (something like indirect(“B134”))…I tried indirect(copyvalues(… and that doesn’t seem to work either.

Ideas?

[Tizian]

Hi JRDavisUF,

Do you mind clarifying your case a little. Maybe use an example and/or screenshot of your problem

I´m not 100% sure, what your set up is and how the problem occurs.

What I think I got:

1. you are reading data from an Array with 10 elements.
   How do you use indirect() exactly to make this easier?
2. You want to create a data history over all 10 arrays.
3. You want to add a timeseries to the data history.
   Where does the time come from and in what format is the time?

How is indirect() applied here and what is the result you wished for?

I have the indirect pointing to the data at the bottom of the sheet and then use “=” pointing to the indirects in some cells above

I also do not quite understand what you are trying to achieve with INDIRECT(COPYVALUES)

I think, what would help me is an example of your incoming data set, a screenshot of what you are currently doing and how the implemented functions look like

Cheers

Tizian

[JRDavisUF]

Attached is a screenshot.

As there are 10 times, each with 5 observations per time, there are 60 values in to larger table. Rather than referencing each value individually (e.g. Time0: B51, Value @ Time0 for Variable 0: B53), I constructed a indirect function (you can see this function in the attached to the right of f(x) ) which takes advantage of the pattern of 8 rows between each time, variable value to extract all of the data. With this function created, I can copy it to each element in the table. Much easer to create a indirect-based table than referencing each value individually. This table (larger bordered table in the attached) updates in real-time as expected.

Now, to get the time series, I point cells to the values returned by the indirect function. The smaller table in the attachment get the times, values for the 0th variable (direct assignment). Then the row above, grabs that value directly (t (-1)), and the row above that the same (t (-2)). However, rather than giving me a timeseries, it just returns the same/current value of the inbox.

Using copyvalues was just my attempt to getting around the simple “=” assignment not giving me a timeseries as expect when it points to an indirect.

[Tizian]

Thank you for the new input, I´m still lacking a bit of information here
There is an option in the sheet menu to display all formulas instead of the values:

That will help me a lot.
Could you make another screenshot of the Sheet with this option turned on?

Further, what I already see is, that you will not get the right results if your table is in not below all READ formulas. If that is a lot of space to be used for you, you can either use a different sheet or transpose the READ() function. User ctrl while drag to do so.

Another way could maybe also be the usage of the loop element for arrays for example:

Transforming, filtering, pivoting JSON table

Hi, Thank you for this question. We have not had yet time to explain this in detail, but a tutorial will come There are two ways. Either you know the amount of array elements in your json and want to calculate everything in one calculation step, then I suggest to work with the array elements as usual READ objects. If you need, you could even use the IF-Cells to add further dependencies. Or you choose the loop option. It is possible to loop through inbox messages. This turns o…

But that’s just for inspiration.

[JRDavisUF]

I moved the tables to the bottom and am now displaying the formulas. See attached. Unfortunately, the issue persists.

I saw the loop-related topic earlier and was thinking it would work better for my use case anyway, but to be honest, I didn’t understand it (upload://e8ysBTZCox3lJH1fc8GuT4XicNl.png) I’ll play around with it again and see if I can figure it out…

[JRDavisUF]

[JRDavisUF]

Also, FWIW, it’s telling me there is a 50 col limit so I cannot transpose.

[Tizian]

Thank you for the explanation, I now get what you did.

The problem you are having is based on the way indirect works.
It is not actually getting the value from one cell to another like your intentions were.
The value an indirect function holds is the reference itself, and linking to an indirect function is basically redirecting a reference.

Example:
So INDIRECT("B5") in A1 and a reference to A1 in another cell will result in =B5 and not the value displayed in A1.

You can check this by e.g. using TEXT(A1,"#,#"). This will not give you the value of B5, but will treat it like: TEXT(INDIRECT(B5),"#,#") and will show B5

This is why your data history does not work, because it is basically a list of references to the same cell.
In your case you will have to directly link to the original cell, then a data history is possible.

I understand, that this may not be obvious and I will clarify this in the documentation.
Further, I will start a conversation in the team, if the implementation of the indirect function should be changed. Because this behavior is not apparent and does have implications on all use cases, where order relationship is at play.

PS: Yes, I did not think about the Col limit. Transposing does not work for your use case.
I think using the loop could help you.
What loop does, is basically cutting one message into multiple.
You could then fill your table based on if clauses and index numbers.
If you add a loop element and drag a READ function to the sheet, the READ() function will look differently, then without the loop. This is because it will blank a hierarchy, so the READ() function will work on any array. The interpretation of the drag to READ() movement is based on which level you add the loop element.
E.g.:

=READ(INBOXDATA(,,"0","value"),H8,"Number",,TRUE)

=READ(INBOXDATA(,,,"value"),H8,"Number",,TRUE)

Maybe that was the confusing part.
Let me know if you have more questions.

Cheers Tizian

[JRDavisUF]

OK, gotcha. I don’t think you necessarily need to change the indirect method other than perhaps updating the docs as you mentioned as I got the “copyvalues” approach working. That is, I use an indirect to first get the value, then i use a copyvalues to put the number (not the reference) into a different cell. I then reference that cell from above and get the previous time step value. I’m guessing this wasn’t working earlier because I had my table in the middle of the data instead of below it.

Yes, it was the dropping of the looped element that was confusing. I have that method working now as well.