5 Replies Latest reply on Jul 3, 2014 10:30 AM by Michael Lance

    Display based on most recent date (simple)

    Michael Lance

      Hello.

       

      I have seen more complex versions of this, but I simply need to know:

      1. How to set up a filter that automatically selects data/displays rows based on the most recent date.

      -I am using an "as of" column to record the date every time I add data to a table. This is the date field to use for filtering.

      2. Is there a better way to do this?

      3. Why is this so complex?

       

      Thanks,

      Michael

        • 1. Re: Display based on most recent date (simple)
          Shawn Wallwork

          Simplier than what?

           

          TODAY() gives you today's date.

          WINDOW_MAX(MAX([Date]))) gives the maximum date in the view.

          IF LAST()=0 THEN [Date] END can also give you the last date.

           

          Are these simplier?

           

          --Shawn

          • 2. Re: Display based on most recent date (simple)
            Michael Lance

            Hi Shawn,

             

            I tried the formulas that you sent but they did not work for me for some reason.

            I've attached the packaged workbook and Excel file I'm working with.

            The end result that I need is one row per record with the language (Latin or blank) and attendance rate (should be 70) corresponding to the most recent date (11/25/13).

             

            Thanks for your help!

             

            -Michael

            • 3. Re: Re: Display based on most recent date (simple)
              Shawn Wallwork

              See attached. Let me know if you have questions, or need something different.

               

              --Shawn

              • 4. Re: Display based on most recent date (simple)
                Rossella Blatt Vital

                Hi,

                I have a similar problem but can't figure out how to adapt this solution to my case. Could you please advise?

                The main difference is that in my view there is also a parameter that allows the user to see the view for either ALL dates or ONLY the MOST RECENT DATE.

                 

                Thanks for any help

                Rossella

                • 5. Re: Display based on most recent date (simple)
                  Michael Lance

                  Hi again,

                   

                  Adding a layer to this, I want to now be able to combine the calculated field you provided with one that specifies the school.

                  As a recap, last time you provided a calculated field that identifies the row (where there are >1 per individual) that has the most recent "AsOfAtt" record from the Attendance table and display that record with the corresponding records on the joined table.

                   

                  Now, I need to combine that calculation/logic with a calculation that indicates the correct school to which a student belongs (see attached updated Excel and Tableau files).

                  I need a display that does the following:

                  1. Identifies the record with the most recent date for "AsofAtt" in the "Attendance" table.

                  2. Uses the corresponding "School" such that filtering by only that school will show the row.

                  3. Still uses the correct language ("Latin" or "Null") from the "Demog" table: if "Latin" occurs for any row for a record (student), then it should be included.

                   

                  I have already tried using IF (School = "1" AND max([AsOfAtt]) = attr([AsOfAtt])) THEN "School One" but when I finally got the calculation to work out, the filter would not work on the page due to the aggregation level (If I recall correctly).

                   

                  I am also interested in using this in the future with different types of view (summary data instead of row by row).

                  I've attached an updated Excel file with the school field.

                   

                  Thanks!

                  -Michael