Search code examples
calculated-columnsspotfire

Spotfire Calculate column: Time difference OVER column value

Folks,

I have a data table:

My data table

I am trying to achieve recurring calculations and having trouble doing so.

I have managed to do it per single master code as a calculated column, and trying to achieve all the calculated time differences to be in single calculated column as image above.

Any help appreciated!

Thanks.

PS: The data is spanned over a range (1-200 days etc) and a 5 second difference from one row to another with a possible change in Master code. I am trying to obtain the period per code per iteration.

Hope I am clear.

Solution

  • Here are the steps you need to take

    • Insert calculated column #1 below and name it ROWID
    • Insert calculated column #2 below and name it DateTime
    • Insert calculated column #3 below and name it Grouping
    • Insert calculated column #4 below and name it DateDifference

    Expression 1

    RowId()

    Expression 2

    DateTime(Concatenate([Date]," ",[Time]))

    Expression 3

    Min(If(([Master code]<>Max([Master code]) over (Next([ROWID]))) or ([ROWID]=Max([ROWID])),[ROWID] - Min([ROWID]) over (AllPrevious([ROWID])))) OVER (AllNext([ROWID]))

    Expression 4

    Abs(DateDiff(Min([DateTime]) OVER (Intersect([Grouping],AllPrevious([ROWID]))),[DateTime]))

    If you really only want to populate the rows you listed above, then continue from here...

    • Insert calculated column #5 and name it RowsToKeep

    Expression 5

    If(([Master code]<>Max([Master code]) over (Next([ROWID]))) or ([ROWID]=Max([ROWID])),[DateDifference])

    RESULTS

    +-------+------------+-------------+------------------+-------------+----------+----------------+------------+
| ROWID |    Date    |    Time     |     DateTime     | Master code | Grouping | DateDifference | RowsToKeep |
+-------+------------+-------------+------------------+-------------+----------+----------------+------------+
|     1 | 12/07/2017 | 1:05:40 AM  | 12/07/2017 1:05  |         100 |        0 | 0:00:00        |            |
|     2 | 12/07/2017 | 4:45:20 AM  | 12/07/2017 4:45  |         100 |        0 | 3:39:40        |            |
|     3 | 12/07/2017 | 6:20:10 AM  | 12/07/2017 6:20  |         100 |        0 | 5:14:30        | 5:14:30    |
|     4 | 12/07/2017 | 6:20:11 AM  | 12/07/2017 6:20  |         400 |        4 | 0:00:00        |            |
|     5 | 12/07/2017 | 8:15:12 AM  | 12/07/2017 8:15  |         400 |        4 | 1:55:01        |            |
|     6 | 12/07/2017 | 9:12:10 AM  | 12/07/2017 9:12  |         400 |        4 | 2:51:59        |            |
|     7 | 12/07/2017 | 9:45:45 AM  | 12/07/2017 9:45  |         400 |        4 | 3:25:34        | 3:25:34    |
|     8 | 12/07/2017 | 9:45:46 AM  | 12/07/2017 9:45  |         120 |        6 | 0:00:00        |            |
|     9 | 12/07/2017 | 10:20:35 AM | 12/07/2017 10:20 |         120 |        6 | 0:34:49        | 0:34:49    |
|    10 | 12/07/2017 | 10:20:36 AM | 12/07/2017 10:20 |         100 |       10 | 0:00:00        |            |
|    11 | 12/07/2017 | 10:25:45 AM | 12/07/2017 10:25 |         100 |       10 | 0:05:09        |            |
|    12 | 12/07/2017 | 11:15:55 AM | 12/07/2017 11:15 |         100 |       10 | 0:55:19        |            |
|    13 | 12/07/2017 | 12:22:22 PM | 12/07/2017 12:22 |         100 |       10 | 2:01:46        | 2:01:46    |
|    14 | 12/07/2017 | 12:22:23 PM | 12/07/2017 12:22 |         350 |       16 | 0:00:00        |            |
|    15 | 12/07/2017 | 3:35:40 PM  | 12/07/2017 15:35 |         350 |       16 | 3:13:17        |            |
|    16 | 12/07/2017 | 3:42:25 PM  | 12/07/2017 15:42 |         350 |       16 | 3:20:02        |            |
|    17 | 12/07/2017 | 4:38:17 PM  | 12/07/2017 16:38 |         350 |       16 | 4:15:54        |            |
|    18 | 12/07/2017 | 4:52:35 PM  | 12/07/2017 16:52 |         350 |       16 | 4:30:12        |            |
|    19 | 12/07/2017 | 4:52:40 PM  | 12/07/2017 16:52 |         350 |       16 | 4:30:17        | 4:30:17    |
|    20 | 12/07/2017 | 4:52:41 PM  | 12/07/2017 16:52 |         100 |       19 | 0:00:00        |            |
|    21 | 12/07/2017 | 7:23:23 PM  | 12/07/2017 19:23 |         100 |       19 | 2:30:42        |            |
|    22 | 12/07/2017 | 8:23:20 PM  | 12/07/2017 20:23 |         100 |       19 | 3:30:39        | 3:30:39    |
+-------+------------+-------------+------------------+-------------+----------+----------------+------------+