1 2 Previous Next 15 Replies Latest reply on May 21, 2019 2:38 PM by Joe Oppelt

# How to get latest Date?

Hi,

How to get the latest date of each item?

Thank you,

Lawrence

• ###### 1. Re: How to get latest Date?

I would use the WINDOW_MAX table calc, restarting every [Name]

2 of 2 people found this helpful
• ###### 2. Re: How to get latest Date?

Hi Joe,

Can you elaborate on the window_max

i tried max but it shows June 24 2016

Thank you

• ###### 3. Re: How to get latest Date?

Hi Lawrence,

Yes you can use table calculations, as Joe pointed out, by creating a calculated field with WINDOW_MAX(MAX([Your Date])) and editing your calculation to restart every [Name], but I think that the easiest solution would be by LOD Expressions (Overview: Level of Detail Expressions). Just create a calculation with {FIXED [Name] : MAX(MDY(TS))} and you are good to go.

Here (Top 15 LOD Expressions | Tableau Software ) you can find more interesting examples of LOD calcs

Best,

Hil

3 of 3 people found this helpful
• ###### 4. Re: How to get latest Date?

See attached superstore example.  (Version 8.2 workbook.)

In sheet 4, you get one date for the whole sheet -- just like you described.

In Sheet 5 I edited the table calc to restart every [Continent].

In Sheet 7 I messed with index on the filter (also a table calc, also restarting every continent) to get you just one row per continent.

If you're on 9.0 or higher, you can also do this with LODs.  I just like doing table calcs for some reason.  Tableau and I have a thing going on with table calcs, ever since I had a table calc epiphany some time back.

1 of 1 people found this helpful
• ###### 5. Re: How to get latest Date?

Hi sorry I' am so beginner to this

What do you mean "to restart every Name"?

• ###### 6. Re: How to get latest Date?

Hi everyone

This is now solved

make a calculated field {Fixed [Name] : MAX ([TS])}

Drag to Rows and set date to MDY

Thank you ALL

-Lawrence

• ###### 7. Re: How to get latest Date?

How did you get the date to restart for every continent?

• ###### 8. Re: How to get latest Date?

Edit the table calc:

You get this screen:

By default Tableau usually picks Table(down) or Table(across), and then the table calc jut plows through all the marks sequentially.  But you can tell it to "walk" through the table differently.

Here I told it to obey my choices of dimensions.  I have it do the continents in order, and within each continent to walk through each date value.  And then there is a place where I can tell it to "restart every", and I selected Continent.  (If I had selected NONE, then it would just keep plowing down the table, going from the last day in one continent, to the first day in the next.)

We have a lot of control over table calcs in here, and it could be a hour long tutorial to discuss all the features and nuances and settings.

• ###### 9. Re: How to get latest Date?

How would you apply the logic here to a field that wasn't placed under measure value?

Please see attached workbook for an example

• ###### 10. Re: How to get latest Date?

I have this open, but I'm not sure what you are asking.  What are you looking to do in here?

• ###### 11. Re: How to get latest Date?

I want to show the order ID for the latest date - here you see several orders and dates, and want to only show 1 order per product name based on the latest date

MORE CONTEXT:

I have a workbook that lists an item then shows all POs for that given item. I only want to show the latest PO for that item though, by the latest request date.

I've seen instructions online to use {FIXED[PO Number]:MAX([Date Requested])} but am not getting that to work. The sample workbook recreates the problem I'm having, based on superstore data.

• ###### 12. Re: How to get latest Date?

Back when this thread was started, it was the dawn of the new feature called LODs.  You'll notice that my sample workbook was an 8.2 workbook.  LODs came out with 9.0.  (and the person who started this thread did his solution with LODs.)

In the attached I did what you need both ways.

On Sheet 1 I used a table calc.  It's displayed after the date.  Edit the Last Order Table Calc settings and you'll see this:

I restart every product name.  The calc says to compare the date of the current row with the WINDOW_MAX date among all the dates for that Product.  If matches, then grab the order ID.

Notice that this calc has to be a measure.  All table calcs have to be a measure.  And notice that when I reference Date or OrderID in the calc, I have to wrap it in ATTR().  These are limitations and requirements of table calcs.

On Sheet 1(2) I did the same thing with LOD.  Here the calc is [Last order LOD].  Same logic, but I don't have to mess with ATTR(), and a FIXED LOD can even be a dimension.  Same results.

Each method (LOD vs table calc) has its advantages and its drawbacks.  Each has power and weaknesses.  In this case, the LOD is the easier way to do it, but either way works just as well and gets the job done.

See attached.

1 of 1 people found this helpful
• ###### 13. Re: How to get latest Date?

Thank you! That worked

• ###### 14. Re: How to get latest Date?

Hi Team,

I have same scenario like above but i am unable to achieve the results below is my source data

 test area testcas testno testid Test Case Status Start Timestamp End Timestamp abc bac 123 2833 Fail 5/20/2019 16:47 5/20/2019 16:47 abc bac 123 2833 Pass 5/20/2019 19:05 5/20/2019 19:06 abc bac 123 2844 Pass 5/20/2019 17:41 5/20/2019 19:05 abc bac 123 2844 Skipped 5/20/2019 16:46 5/20/2019 16:46

I have to print the latest time stamp results i tried above but couldn't achieve it someone help me out to do that

 testarea testcas testno testid Test Case Status Start Timestamp End Timestamp abc bac 123 2833 Pass 5/20/2019 19:05 5/20/2019 19:06 abc bac 123 2844 Pass 5/20/2019 17:41 5/20/2019 19:05

Thanks

Hema

Tha

1 2 Previous Next