Search code examples
calculated-columnsspotfire

Spotfire how to make calculation along one coloumn

I have a data table as below.I would like to create a new column called "Time Difference" which captures the time difference between [STAGE] start and [STAGE] end. Is there any way to do it without using data transformation?

Thank you!

enter image description here

enter image description here

Solution

  • Assuming each ID is unique, and your data is ordered by Time where Stage = Start is the first row for each ID and Stage = End is the last row for each ID, you can use this:

    Concatenate(Min([Time]) OVER ([ID]),"-",Max([Time]) over ([ID]))

    RESULTS

    +----+---------+---------+------+-----------------+
| ID |  Stage  | Action  | Time | Time Difference |
+----+---------+---------+------+-----------------+
|  1 | Start   | approve | A    | A-F             |
|  1 | Process | approve | B    | A-F             |
|  1 | Process | approve | C    | A-F             |
|  1 | Process | approve | D    | A-F             |
|  1 | Process | decline | E    | A-F             |
|  1 | End     | approve | F    | A-F             |
|  2 | Start   | approve | G    | G-I             |
|  2 | Process | decline | H    | G-I             |
|  2 | End     | approve | I    | G-I             |
+----+---------+---------+------+-----------------+

    If your data isn't already sorted, you can just apply a Rank() to fix this. Let me know if that's the case.

    EDIT WITH NEW DATA

    EXPRESSION

    Concatenate(Min(If((Upper([Stage])="START") and (Upper([Action])="APPROVE"),Max([Time]) OVER ([ID]))) OVER ([ID]),"-",Min(If((Upper([Stage])="END") and (Upper([Action])="APPROVE"),Max([Time]) OVER ([ID]))) OVER ([ID]))

    Simplified

    Concatenate(Min(If((Upper([Stage])="START") and (Upper([Action])="APPROVE"),[Time])) OVER ([ID]),"-",Min(If((Upper([Stage])="END") and (Upper([Action])="APPROVE"),[Time])) OVER ([ID]))

    RESULTS

    +----+---------+---------+------+-----------------+
| ID |  Stage  | Action  | Time | Time Difference |
+----+---------+---------+------+-----------------+
|  1 | On hold | decline | A    | C-H             |
|  1 | Start   | decline | B    | C-H             |
|  1 | Start   | approve | C    | C-H             |
|  1 | Process | DECLINE | D    | C-H             |
|  1 | Process | approve | E    | C-H             |
|  1 | Process | approve | F    | C-H             |
|  1 | End     | decline | G    | C-H             |
|  1 | End     | approve | H    | C-H             |
|  2 | Start   | approve | I    | I-K             |
|  2 | Process | decline | J    | I-K             |
|  2 | End     | approve | K    | I-K             |
+----+---------+---------+------+-----------------+