8 Replies Latest reply on Mar 29, 2016 1:55 PM by Kris Taylor

    Dynamic Max Date

    Wizard Lee

      Hi all Tableau experts

       

      I would like to know how to get the maximum date from a data set to use in a comparison. I need to get this date value because the data gets loaded for a particular month, and it could be another number of months before new data is then loaded, so using functions like Today() or Now() will not work in this particular case.

       

      I know we can use table calculations to get this value, but the date field needs to form part of the view, what if I don't want this date field to be part of the view? Therefore table calc becomes redundant in this particular case. How do we go about achieving the same thing when this occurs?

       

      I'm fairly new to Tableau and am finding it extremely difficult that sub queries can only be created using custom SQL, I'm an end user and my data source connections are restricted by our I.T. team, so only have access via Tableau Server and besides, don't want to create separate data source connections tailored to a specific view.

       

      Any help on this would be greatly appreciated

       

      Wizard

        • 1. Re: Dynamic Max Date
          Xavier P

          Hi Wizard, have you tried MAX([Date]) ?

          • 2. Re: Dynamic Max Date
            Wizard Lee

            Hi Xavier

             

            max([Date]) unfortunately does not provide what I'm after, Maybe the below example data set will provide a better example:

            max date data set.jpg

            So firstly I want to compare the count of units in September 2013 to the count of units in August 2013 and provide a variance between the two months. Secondly, I don't want the batch date field in the view as shown, it will be replaced by a region field, this is what i mean by not being able to use table calcs as the field will not be used in the view.

             

            And lastly I want to use a max function to return the latest month in the data and then possibly leverage this to then work out the prior month from this max date value. I want to do this to keep the viz dynamic so month on month I won't need to go into the view to manually change it.

             

            Hope this makes more sense now. Thanks

            Wizard

            • 3. Re: Dynamic Max Date
              Prashant Sharma

              Hi,

              If you want to compare two date fields & wants to show data according to these fields then use two parameter on which you can easily show whatever you want. If possible provide some sample packaged workbook with snapshot of your requirement.

               

              Warm Regards,

              Prashant Sharma - India | LinkedIn

              • 4. Re: Dynamic Max Date
                Joshua Milligan

                Wizard,

                 

                This is probably one of those questions where providing a packaged workbook will help greatly.  There may be multiple approaches -- and don't discount table calcs, because even though the date field will need to be in the view, there are approaches for hiding it in the final viz.

                 

                Have you tried simply filtering date by the TOP 1 or 2 Max(Date)?  You can do that at any level of granularity (top 2 months, top two days, etc...).

                 

                0.png

                 

                Regards,

                Joshua

                • 5. Re: Re: Dynamic Max Date
                  Wizard Lee

                  Hi Joshua

                   

                  Please find attached workbook

                   

                  The Variance worksheet is the final view I'm trying to get to. I guess not sure how the above techniques will work, cause I want to show the comparisons side by side

                   

                  Regards

                  Wizard

                  • 6. Re: Re: Re: Dynamic Max Date
                    Joshua Milligan

                    Wizard,


                    Here is a proposed solution (it does use table calculations!)  Basically, it looks up the last and next to last sales along the order date and a third calculation computes the variance.  I've filtered the view to include only the final month of data.  In the real world, you'd want to include this filter and narrow it as tight as possible for performance (as the table calc filter will be applied after all the source data is returned).

                     

                    Also, the IF Last() == 0 check in the calcs is for performance.  I'd be happy to answer any questions you might have!

                     

                    Regards,

                    Joshua

                     

                    0.png

                    • 7. Re: Re: Re: Dynamic Max Date
                      Wizard Lee

                      Hi Joshua

                       

                      That worked a treat! Thank you so much for your help, greatly appreciated. I totally understand what the view is doing, very smart!

                       

                      Regards

                      Wizard

                      • 8. Re: Dynamic Max Date
                        Kris Taylor

                        I hope that it is not too late to get a response in, but I have similar needs as described in the original post.  I use the MAX function inside a LOD calculation as follows to meet most of my needs related to the Max data set date:

                         

                        {MAX([Ship Date])}