How to Calculate a difference between rows (not columns)

Question & Answer
,
1

See below the table I am working with
This is a time series of actions and I try to understand how I can calculate the time difference between the individual rows that are returned

How can I calculate the difference between the time stamp in
row 2 and row 1
row 3 and row 2
row 4 and row 3
etc
thanks!

|Step|Location|User|Time Stamp|Difference in Minutes|
|Step 1|Valve A|Frank|12/22/2021 8:00|0|
|Step 2|Valve B|Mary|12/22/2021 10:00|120|
|Step 3|Valve C|John|12/23/2021 10:00|1440|
|Step 4|Valve A|Bill|12/23/2021 10:31|31|
|Step 5|Valve C|Carl|12/23/2021 10:45|14|

image
image748×114 3.7 KB

2

Hi @mlinsin,

Here is an example solution to your question. You can see the implementation in this QuickSight Arena dashboard: Link to Arena Dashboard.

Steps to Solve:

  1. Create a Lag Timestamp Field:

    • Use the lag function to capture the timestamp from the previous row.
    c_lag_timestamp = lag({Time Stamp}, [{Time Stamp} ASC], 1, [])

  2. Calculate the Time Difference:

    • Use the dateDiff function to calculate the difference in minutes between the current row’s timestamp and the lag timestamp.
    c_lag_timestamp_difference = dateDiff({c_lag_timestamp}, max({Time Stamp}), "MI")

  3. Handle Null Values:

    • Use the ifelse function to handle null values, ensuring that the first row (which has no previous row) shows a difference of 0.
    c_clean_difference = ifelse(isnull({c_lag_timestamp_difference}), 0, {c_lag_timestamp_difference})

Note on Table Calculations:

These are considered table calculations in QuickSight, meaning they operate on the data that is visible in your visual. For the lag function to work correctly, both the previous and current rows must be present in your visual. If the previous row is not displayed in your visual, the lag function won’t be able to retrieve its value, and the calculation won’t work as intended.

1 Like
3

thanks a lot for the hint, I was not aware of the lag function and this did the trick!!