Tableau create unique max date

Question

I Need to get the Unique value of Max([Date]).

I have this calculation for max date:

{ FIXED [City] : Max([Date]) }

Count :

IF[Max Date (Last Street)]= [Date] 
THEN [Count Record]
Else 0
END

For Example:

City    Date              Street        I get (Count)        I Want (Count)
Miami   01/01/2019        1st            0                    0
Miami   01/02/2019        2nd            0                    0
Miami   01/03/2019        3rd            1                    0
Miami   01/03/2019        4th            1                    1

MonteCarloSims MonteCarloSims · Accepted Answer · 2019-04-19T04:39:47

This would be a good situation to mix LOD calculations and Table Calculations. Your initial LOD function looks good, as it will find the complete max date per each city. From there, you can apply the concept of the calculated field you already have started and add a Table Calculation (Last()):

IF ATTR([Max Date (Last Street)]) = ATTR([Date]) 
    AND LAST() == 0
THEN [Count Record]
Else 0
END

Note that the other portions of the calculated field are wrapped in ATTR() to make them into aggregations.

Once you add additional cities back into the data, you'll have to edit the table calculation by Right clicking on table calculation on view > Edit Table Calculation...

Take note of the fact that Specific Dimensions is selected and Restarting Every is changed to "City"

Final product should look like this:

Alternative Method:

If you'd like to purely use LODs and your street names always contain unique ascending numbers:

If Date = {Fixed [City]: MAX(Date)} 
    AND REGEXP_EXTRACT([Street],'(\d+)') = {FIXED [City], [Date]: 
        MAX(REGEXP_EXTRACT([Street],'(\d+)'))}
Then 1
Else 0
END

The above will essentially extract the number from the street then add it as a condition in addition to the MAX(Date) which already exists. Then the you will only get a 1 when both conditions have been met.

The end result will be the same as above.

Tableau create unique max date

1 Answers