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

    How to get latest Date?

    lawrence.magno

      Hi,

       

      How to get the latest date of each item?

       

      6-24-2016 1-13-04 PM.png

       

      Thank you,

      Lawrence

        • 1. Re: How to get latest Date?
          Joe Oppelt

          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?
            lawrence.magno

            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?
              Hildebrando Souza Jr

              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?
                Joe Oppelt

                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.

                • 5. Re: How to get latest Date?
                  lawrence.magno

                  Hi sorry I' am so beginner to this

                   

                  What do you mean "to restart every Name"?

                  • 6. Re: How to get latest Date?
                    lawrence.magno

                    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?
                      Dan Maycock

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

                      • 8. Re: How to get latest Date?
                        Joe Oppelt

                        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?
                          Dan Maycock

                          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?
                            Joe Oppelt

                            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?
                              Dan Maycock

                              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?
                                Joe Oppelt

                                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?
                                  Dan Maycock

                                  Thank you! That worked

                                  • 14. Re: How to get latest Date?
                                    Hema Manimaran

                                    Hi Team,

                                     

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

                                     

                                          

                                    test areatestcastestnotestidTest Case StatusStart TimestampEnd Timestamp
                                    abcbac1232833Fail5/20/2019 16:475/20/2019 16:47
                                    abcbac1232833Pass5/20/2019 19:055/20/2019 19:06
                                    abcbac1232844Pass5/20/2019 17:415/20/2019 19:05
                                    abcbac1232844Skipped5/20/2019 16:465/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

                                     

                                     

                                    testareatestcastestnotestidTest Case StatusStart TimestampEnd Timestamp
                                    abcbac1232833Pass5/20/2019 19:055/20/2019 19:06
                                    abcbac1232844Pass5/20/2019 17:415/20/2019 19:05

                                    Thanks

                                    Hema

                                     

                                     

                                     

                                     

                                     

                                     

                                    Tha                                                                                                  

                                    1 2 Previous Next