Tableau: calculating offset without table functions

Question

I have the following datasource, which contains some tickets status.

My target is to calculate the "Status previous from" column, which contains the timestamp when the previous state started. My goal would be to calculate it without using table calculations so I can make a pill and anyone can use them in a view.

Is it possible? I tried many Lods functions but I didn't get anything useful.

+--------+---------------------+---------------------+-------------------------+-------------------------+----------------------+
| Ticket |     Status from     |      Status to      |     Status previous     |     Status current      | Status previous from |
+--------+---------------------+---------------------+-------------------------+-------------------------+----------------------+
| A001   | NULL                | 10/12/2022 13:40:00 | NULL                    | New                     | NULL                 |
| A001   | 10/12/2022 13:40:00 | 10/13/2022 10:00:00 | New                     | Ready                   | NULL                 |
| A001   | 10/13/2022 10:00:00 | 10/13/2022 11:27:03 | Ready                   | Development in progress | 10/12/2022 13:40:00  |
| A001   | 10/13/2022 11:27:03 | 10/14/2022 18:45:55 | Development in progress | Ready for tests         | 10/13/2022 10:00:00  |
| A001   | 10/14/2022 18:45:55 | 10/14/2022 18:49:55 | Ready for tests         | Tests in progress       | 10/13/2022 11:27:03  |
| A001   | 10/14/2022 18:49:55 | 10/14/2022 19:38:00 | Tests in progress       | Test passed             | 10/14/2022 18:45:55  |
| A001   | 10/14/2022 19:38:00 | NULL                | Test passed             | Done                    | 10/14/2022 18:49:55  |
| A002   | NULL                | 10/17/2022 1:42:56  | NULL                    | New                     | NULL                 |
| A002   | 10/17/2022 1:42:56  | 10/17/2022 18:44:44 | New                     | Ready                   | NULL                 |
| A002   | 10/17/2022 18:44:44 | 10/18/2022 6:05:05  | Ready                   | Development in progress | 10/17/2022 1:42:56   |
| A002   | 10/18/2022 6:05:05  | 10/18/2022 9:27:34  | Development in progress | Ready for tests         | 10/17/2022 18:44:44  |
| A002   | 10/18/2022 9:27:34  | 10/18/2022 12:00:01 | Ready for tests         | Tests in progress       | 10/18/2022 6:05:05   |
| A002   | 10/18/2022 12:00:01 | 10/18/2022 12:01:01 | Tests in progress       | Test failed             | 10/18/2022 9:27:34   |
| A002   | 10/18/2022 12:01:01 | 10/18/2022 18:07:08 | Test failed             | Development in progress | 10/18/2022 12:00:01  |
| A002   | 10/18/2022 18:07:08 | 10/18/2022 20:40:40 | Development in progress | Ready for tests         | 10/18/2022 12:01:01  |
| A002   | 10/18/2022 20:40:40 | 10/18/2022 23:34:52 | Ready for tests         | Tests in progress       | 10/18/2022 18:07:08  |
| A002   | 10/18/2022 23:34:52 | 10/18/2022 23:44:11 | Tests in progress       | Test passed             | 10/18/2022 20:40:40  |
| A002   | 10/18/2022 23:44:11 | NULL                | Test passed             | Done                    | 10/18/2022 23:34:52  |
+--------+---------------------+---------------------+-------------------------+-------------------------+----------------------+

score 1 · Answer 1 · answered Oct 17 '22 at 17:04

1

LOD expressions won't work here as you are looking to read from and use data within the physical window. Wouldn't a DateDiff work for you? DateDiff('seconds',[Status from],[Status to])

Steve

answered Oct 17 '22 at 17:04

Steve Martin

113
7

score 0 · Accepted Answer · answered Oct 17 '22 at 19:16

Table calculations are the only calcs in Tableau that can take the order of records into account. Aggregate and LOD calcs operate on unordered blocks of records, so can't calculate a "difference from previous" value.

If you don't want to use Table calcs - say to avoid shipping large volumes of detailed data records across the wire to the client, you could look at using a windowing (aka analytic) query via custom SQL or pre-calculate this column with a pre-processing script.

SQL Windowing/analytic queries are very good for this (and other) purposes, so worth the time to learn. If you use custom SQL in Tableau, I recommend limiting it to creating one apparent table/view in your logical model and relating that to the other tables. You don't have to make one monster custom SQL batch for everything.

Tableau Prep has some support a few limited windowing/analytic queries but far fewer than the SQL standard. Tableau's hyper database supports windowing/analytic queries via its API, but I don't believe you can use custom SQL with hyper (yet) unfortunately.

Tableau: calculating offset without table functions

2 Answers2