3 Replies Latest reply on Nov 19, 2016 1:02 AM by Marc MOUSSA

    Display most recent value using 2 date fields

    Mark Fagan

      Hello,

      I have a dataset that is updated weekly that has weekly, 6 month, and year-to-date values in 3 separate columns.  For the weekly column ONLY, the value displayed COULD change when the data run is completed for following week with an updated value but the prior week value is retained for historical purposes.   What I am looking to do is to ALWAYS display the most recent value for that "period last day" column based on the latest "report date" field but I am unsure on how to accomplish this in the calculated field.

       

      The results in Tableau shown here currently overlap in the first column (weekly) since it is pulling in both records even though the "report date" is different.  I am looking for a result of displaying 56 on record 1 and 67 on record 4.  I am using a string calculated field for each column:

      Display latest value overlapping.JPG

       

      Here is the code for the calculated field and what the sample data looks like.  You can see that the South and West region had an update for 11/5/2016 when the report was ran again on 11/12/16. I want to be able to only display the latest value based on the report date of 11/12/16.  How would I approach this to look at the "period last day" column and then only show the "display value" of the latest "report date column"?:

      Display latest value.JPG

      I already tried using a filter top 1 max for the "report date" field but I only need that to apply to the 1st column of the results...

       

      Thanks in advance for any direction....

       

      -Mark

        • 1. Re: Display most recent value using 2 date fields
          Rekha Kesavan

          Hello Mark,

           

          If you are looking to find the data related to latest date only, use Max function with Report Date field as it will pick only the latest(or last day) available for the record when there are multiple entries. You dont have to add filter as it will filter your rows as well.

           

          Max(ReportDate)

           

          If you could add the .twbx workbook with sample data, it would be helpful to get the answer as your desire.

           

          Thanks

          Rekha

          • 2. Re: Display most recent value using 2 date fields
            Mark Fagan

            Hi Rekha,

             

            Thank you for your response.

             

            Some other information that may help is that the user has a parameter to choose the "Period End date" (NOT the "Report Date")

             

            Once I created the calc field and dragged to the filter the blue pill changed to AGG(Max Date Report Final) I edited the filter to select "true".  Once I did this and selected 11/5/16 from the parameter the result removed the 2nd and third columns of data(6 month and year-to-date data) but the weekly data column was correct.  Note that if I selected 11/12/16 from the parameter, all three columns displayed correctly since that is the latest "report date" dataset.

             

            I cannot provide the twbx file at this time since it contains sensitive data and no time today to create a mock-up.

             

            Thank you,

             

            Mark

            • 3. Re: Display most recent value using 2 date fields
              Marc MOUSSA

              Try this instead { Max(ReportDate) }