CODEWITHSUNDEEP
tableau-apicalculated-columns
Jay
Jay

Reputation: 2902

Tableau: DATEDIFF( 'days', MIN([Start Date]), [End Date])

Cheers!

I'm trying to get a chart working that shows me the count of work orders that are completed each day after work on a unit (serial number) starts. I'd like to be able to "shadow" multiple serial numbers on top of each other, normalized to a start date of '0'.

Currently I have columns in my data set:

Work order number     (0..999), repeats for each serial number
Serial number         (0..999)
Work order start date (Datetime)
Work order end date   (Datetime)

Say for instance that a new serial number starts each day, contains 5 work orders, and requires 5 days to complete (there are 5 units in WIP at any given time).

The data might look like (dates shown as ints):

| Work order number | Serial number | Work order start date | Work order end date |
| ----------------- | ------------- | --------------------- | ------------------- |
| 1                 | 1             | 1                     | 2                   |
| 2                 | 1             | 1                     | 3                   |
| 3                 | 1             | 2                     | 4                   |
| 4                 | 1             | 3                     | 5                   |
| 5                 | 1             | 4                     | 5                   |
| 1                 | 2             | 2                     | 3                   |
| 2                 | 2             | 2                     | 4                   |
| 3                 | 2             | 3                     | 5                   |
| 4                 | 2             | 4                     | 6                   |
| 5                 | 2             | 5                     | 6                   |

I'm assuming I'll need a calculated column that would perhaps go something like:

[Work order end days since start] = 
   [Work order end date] - MIN(
      IF(*serial number matches current*, [Work order start date], NULL)
   )

I (clearly) have no idea how to actually create such a calculated field in Tableau.

The values in the column (same order as the data above) should be:

| Work order end days since start |
| ------------------------------- |
| 1                               |
| 2                               |
| 3                               |
| 4                               |
| 4                               |
| 1                               |
| 2                               |
| 3                               |
| 4                               |
| 4                               |

Any guidance or help? Happy to clarify anything as well. Many thanks! Cheers!

Upvotes: 1

Views: 940

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11896

You will have better results with this kind of data if you reshape it to have a single date column and add a type column indicating whether the current row describes the start or completion of a workorder.

| Work order number | Serial number | date | type |

Think of each row representing a state change, not a work order.

Open work orders on a particular date would be those that have a start record prior to that date, but don't have a completion record prior to that date. If you define a calculated field as +1 if type = New and -1 if type = Completion, then you can use a running total of that field to view the number of open work orders over time.

Upvotes: 1

Related Questions