CODEWITHSUNDEEP
spotfire
Ben Wang
Ben Wang

Reputation: 107

spotfire- how to find create a column to calculate the time of next stage

I want to create a calculated column to show the time action end grouped by [Case ID], [Stage], and [Action]. The order of the stage is not necessary alphabetic and it could be duplicated. Say after [stage] 'C', we could have another [stage] 'c' in the future

Thanks,

enter image description here

Upvotes: 1

Views: 181

Answers (1)

S3S
S3S

Reputation: 25122

Thanks for the updated test data. The data types are extremely important when asking. Also, the test data should mirror the actual data as close as possible, otherwise solutions often will not scale. For example, in the test data the values are only time. Sorting on time doesn't take into account the day, thus it's all treated equally. Since these values are actually DateTime, I have added that to the test data. These expressions will give you the expected results as identified in your question.

  1. Rank([Time_Action_Begin],"asc",[Case ID]) as [Rank]
  2. Min([Time_Action_Begin]) OVER (Intersect([Case ID],Next([Rank])))

RESULTS

+---------+-------+----------+------------------------+------------------------+------+
| Case ID | Stage |  Action  |   Time_Action_Begin    |    Time_Action_End     | Rank |
+---------+-------+----------+------------------------+------------------------+------+
|       1 | A     | approve  | 01/01/2016 11:30:00 PM | 01/02/2016 12:30:00 AM |    1 |
|       1 | A     | approve  | 01/01/2016 11:30:00 PM | 01/02/2016 12:30:00 AM |    1 |
|       1 | B     | approve  | 01/02/2016 12:30:00 AM | 01/02/2016 1:30:00 AM  |    3 |
|       1 | B     | approve  | 01/02/2016 12:30:00 AM | 01/02/2016 1:30:00 AM  |    3 |
|       1 | C     | approve  | 01/02/2016 1:30:00 AM  | 01/02/2016 2:30:00 AM  |    5 |
|       1 | C     | approve  | 01/02/2016 1:30:00 AM  | 01/02/2016 2:30:00 AM  |    5 |
|       1 | D     | approve  | 01/02/2016 2:30:00 AM  | 01/02/2016 3:30:00 AM  |    7 |
|       1 | D     | approve  | 01/02/2016 2:30:00 AM  | 01/02/2016 3:30:00 AM  |    7 |
|       1 | E     | approve  | 01/02/2016 3:30:00 AM  | 01/02/2016 4:30:00 AM  |    9 |
|       1 | E     | approve  | 01/02/2016 3:30:00 AM  | 01/02/2016 4:30:00 AM  |    9 |
|       1 | F     | complete | 01/02/2016 4:30:00 AM  | 01/02/2016 5:30:00 AM  |   11 |
|       1 | F     | complete | 01/02/2016 4:30:00 AM  | 01/02/2016 5:30:00 AM  |   11 |
|       1 | C     | approve  | 01/02/2016 5:30:00 AM  |                        |   13 |
|       1 | C     | approve  | 01/02/2016 5:30:00 AM  |                        |   13 |
|       2 | A     | approve  | 01/01/2016 10:30:00 PM | 01/02/2016 12:30:00 AM |    1 |
|       2 | A     | approve  | 01/01/2016 10:30:00 PM | 01/02/2016 12:30:00 AM |    1 |
|       2 | B     | approve  | 01/02/2016 12:30:00 AM | 01/02/2016 2:30:00 AM  |    3 |
|       2 | B     | approve  | 01/02/2016 12:30:00 AM | 01/02/2016 2:30:00 AM  |    3 |
|       2 | C     | approve  | 01/02/2016 2:30:00 AM  | 01/02/2016 3:30:00 AM  |    5 |
|       2 | C     | approve  | 01/02/2016 2:30:00 AM  | 01/02/2016 3:30:00 AM  |    5 |
|       2 | D     | approve  | 01/02/2016 3:30:00 AM  | 01/02/2016 4:30:00 AM  |    7 |
|       2 | D     | approve  | 01/02/2016 3:30:00 AM  | 01/02/2016 4:30:00 AM  |    7 |
|       2 | E     | approve  | 01/02/2016 4:30:00 AM  | 01/02/2016 5:30:00 AM  |    9 |
|       2 | E     | approve  | 01/02/2016 4:30:00 AM  | 01/02/2016 5:30:00 AM  |    9 |
|       2 | F     | complete | 01/02/2016 5:30:00 AM  | 01/02/2016 6:30:00 AM  |   11 |
|       2 | F     | complete | 01/02/2016 5:30:00 AM  | 01/02/2016 6:30:00 AM  |   11 |
|       2 | C     | approve  | 01/02/2016 6:30:00 AM  |                        |   13 |
|       2 | C     | approve  | 01/02/2016 6:30:00 AM  |                        |   13 |
+---------+-------+----------+------------------------+------------------------+------+

Upvotes: 1

Related Questions