Running Sum of Uptime and Downtime based on Timestamp and Switch Status

Question & Answer
, ,
1

Hello!

I have a table that contains the following fields:

model,
model_serial_number,
timestamp,
run_status

There are several machine models, and several unique serial numbers for each model. If I create a table and filter it to show a single model, and a unique serial number, and then sort by timestamp, it looks like this:

time1 | Off
time2 | On
time3 | Off
etc.

Therefore, the time difference between “time2” and “time1” would be the Downtime - and the time difference between “time3” and “time2” would be the Uptime.

Each serial number could have thousands of entries - and they may begin with either off or on - but they always alternate (switching on and off).

My goal is to determine Total Uptime and Total Downtime for each serial number. I am sure this is relatively simple, but I wouldn’t mind a nudge in the right direction.

Thanks so much!!!

2

This problem could be easier to solve with QuickSight if functions such as lead(), lag() and windowXXX() functions would support PRE_FILTER and PRE_AGG aggregation level (feature request alert :stuck_out_tongue: )

So my first suggestion would be to see if you could get this populated into your dataset (example via a window function from the underlying database).

Nevertheless, failing this, luckily this problem can still be solved totally within QuickSight.
The idea is to sum the differences in time between each 2 readings. Each reading’s timestamp is first converted to a difference in seconds from the first reading’s time. Then this number is is assigned a + or - sign (determined by the uptime_metric_multiplier field). And finally all these are added together to give you the total_uptime. There is some additional logic to cater for cases where the last reading needs to be ignored (see comment at the bottom).

You need to add the following calculated fields:

run_status_int

ifelse(run_status = 'On', 1, 0)

rank_asc

denseRank([timestamp ASC], [model, model_serial_number], PRE_AGG)

rank_desc

denseRank([timestamp DESC], [model, model_serial_number], PRE_AGG)

first_value

minOver(ifelse({rank_asc} = 1, run_status_int, null), [model,model_serial_number], PRE_AGG)

first_timestamp

minOver(timestamp, [model,model_serial_number], PRE_AGG)

diff_first_timestamp

dateDiff({first_timestamp}, {timestamp}, 'SS')

uptime_metric_multiplier

ifelse(
  {rank_desc} = 1 AND run_status_int = 1 , 0, /* Ignore last On metric */
  {first_value} = 1,
    ifelse(
      {rank_asc} % 2 = 0, 1, /* Even metrics are Off */
      {rank_asc} % 2 = 1, -1, /* Odd metrics are On */
      null
    ),
  {first_value} = 0,
    ifelse(
      {rank_asc} % 2 = 0, -1, /* Even metrics are On */
      {rank_asc} % 2 = 1, 1, /* Odd metrics are Off */
      null
    ),
  null
)

total_uptime

sumOver({diff_first_timestamp} * {uptime_metric_multiplier}, [model, {model_serial_number}], PRE_AGG)

This will give you the total uptime. I will leave the definition of downtime for you to figure out :stuck_out_tongue: (it should not be difficult given the above). Also, worth noting here that if a sensor’s last value was On, then currently that last value is ignored since we cannot know the duration for which it remained on.

1 Like
3

@darcoli thanks so much for the help - I really do appreciate it!

Unfortunately this does not work - and I think it might be because of how I stated the problem…

The table that I have does not necessarily alternate from “On” to “Off” every row. It could look like this:

time1 | On
time2 | Off
time3 | Off
time4 | On
time5 | On
time6 | On
time7 | Off

So for the above, the “Uptime” would be (time2-time1) + (time7 - time4).

I think it might be somewhere in the “uptime_metric_multiplier” ? The output of that variable never seems to be null.

Thanks again so much for your help!

4

Hi @dhouser, this problem cannot be easily solved within QuickSight. You will have to do the calculation in your original dataset and then load into QS for visualization.

In the datasource, sounds to me you need to do three steps:

  1. clean up the duplicated on/offs, you just want to keep the first line of multiple on’s or multiple off’s
  2. then you can use the lag window function with offset=1 to bring the neighbor lines into one line, something like:
    time1 | On | time2 | Off
    time2 | Off | time4 | On
    time4 | On |time7 | Off
    time7 | Off
  3. at last calculate duration for the lines with machine on (column 2=on and column 4=off):
    time1 | On | time2 | Off | time2-time1
    time2 | Off | time4 | On | 0
    time4 | On |time7 | Off |time7-time4
    time7 | Off
1 Like
5

@emilyzhu yes that would be the ideal and more intuitive way of going around this. However, currently the lag() function does not support PRE_FILTER or PRE_AGG levels, so getting the total sum is still not possible: row totals in a table visual is empty with fields using lag() function and trying to create another calculated field to SUM() everything will result in nesting of aggregate functions.

So I suppose cleaning up records which do not change states in the dataset query is the best approach and then the same solution i posted earlier would work. In SQL, you could do this

SELECT 
  *
FROM table
WHERE timestamp IN
  (
  SELECT
    timestamp, 
    LAG(run_status) OVER (PARTITION BY model, model_serial_number ORDER BY timestamp) AS previous_run_status
  FROM table
)

(assuming timestamp is unique)

6

Hi @darcoli, you are right. I also recommened the lag window function to run at the data source instead of within QS analysis. In QS, the lag() function is a visual-level table calculation, no further aggregate is supported.