How to fix a calculated field that checks for date in a order?

Question

The data structure resembles this where state visited follows a sequential order of: Hawaii -> California -> Texas -> New York.

| Name       | State       | Date         |
|:-----------|------------:|:------------:|
| Alice      |       Hawaii|   1/1/2019   |
| Alice      |   California|   1/3/2019   |
| Alice      |        Texas|   1/5/2019   |
| Alice      |     New York|   1/7/2019   |
| Bobby      |       Hawaii|   3/20/2019  |
| Bobby      |   California|   3/23/2019  |
| Bobby      |        Texas|              |
| Bobby      |     New York|              |
| Carl       |       Hawaii|   2/10/2019  |
| Carl       |   California|   2/12/2019  |
| Carl       |        Texas|   2/12/2019  |
| Carl       |     New York|              |

I want to show only the date for the people who HAVE not yet been to New York but visited every other place in Tableau.

In this case, this would be Carl since he visited Hawaii, California, AND Texas, but not yet New York.

Alice would not be shown since she already visited New York and Bobby should not be shown since he hasn't been to Texas yet

I tried making a calculated field and dragging that to filter.

IF (ISNULL([Date]) AND [State] = "New York") AND

(NOT ISNULL([Date]) AND 
([State] = "Hawaii" OR
[State] = "California" OR
[State] = "Texas"))

THEN
[Date]

END

I expect the output calculated field to return me the following DATE column:

| Name       | State       | Date         |
|:-----------|------------:|:------------:|
| Alice      |       Hawaii|              |
| Alice      |   California|              |
| Alice      |        Texas|              |
| Alice      |     New York|              |
| Bobby      |       Hawaii|              |
| Bobby      |   California|              |
| Bobby      |        Texas|              |
| Bobby      |     New York|              |
| Carl       |       Hawaii|   2/10/2019  |
| Carl       |   California|   2/12/2019  |
| Carl       |        Texas|   2/12/2019  |
| Carl       |     New York|              |

but all I got was null everything so something in my logic is wrong but I don't know what.

Alex Blakemore Alex Blakemore · Accepted Answer · 2019-07-10T00:26:27

Put Name on the filter shelf. Choose Use All and switch to the Condition tab. Enter the following formula for the condition.

max([State] = "Hawaii") and max([State] = "California") and max([State] = "Texas") and not max([State] = "New York")

Alternatively, you can create a set based on the Name field containing people who have visited the states you want -- using the first 3 lines in the condition above. And another set also based on the Name field containing people who have visited the states you don't want. Then make a combined set that has the people in the first set minus the ones in the second set.

Then you can use sets in filters, calculated fields, and on shelves as desired.

BTW, max([condition]) is true iff any data record satisfies since True is treated as greater than False. min([condition]) is true iff all data records satisfy [condition]

How to fix a calculated field that checks for date in a order?

1 Answers