AnsweredAssumed Answered

DataLink: How can I improve performance with Timed Data, given dynamically changing amount of timestamps?

Question asked by AlbertPhan on Jul 28, 2017
Latest reply on Jun 18, 2019 by PhilippeCloutier

I have developed a spreadsheet that gives a list of timestamps, and the number of rows changes dynamically based on user inputs (start time, end time, interval). Using Timed Data, I also query element data from separate assets based on those timestamps.

 

I pre-populated around 8760 rows with Timed Data because the list of timestamps may vary from 1 row to 8760, and I wanted the spreadsheet to automatically update the queried data if the list grows or shrinks. As a result, the cells read "The time is invalid." if the timestamp cell is blank. I solved this visually by making the text white (thus invisible) if the cell value equals "The time is invalid."

 

However, I am finding that my spreadsheet is incredibly slow each time a user input is changed (e.g. spreadsheet takes 3-5 minutes to update if I change the start time because I have 3 more tabs doing the same thing). I have a hunch that I could improve performance significantly if I find a way to ensure Timed Data is not even used if the timestamp is blank, but using an often used Excel trick of mine to wipe out the array function did not work for some reason (e.g. ={ IF( Timestamp="", "", Timed Data array function ) ) } ).

 

(I know that was a little hard to follow; I can clarify if need be.)

 

Does anyone know of a way to improve the performance here? Possibly by preventing the use of Timed Data for rows with no timestamp?

 

Image of what I mean:

 

Datalink Example Issue.JPG

 

UPDATE: I improved the performance significantly by changing from array function Timed Data to using individual Archive Value and my aforementioned Excel IF() filter. The report is now usable and 3-5 minutes is down to ~5 seconds.

 

Message was edited by: Albert Phan

Outcomes