2 Replies Latest reply on Mar 15, 2012 3:48 AM by Meng Hui

    Display corresponding measure value for latest date

    Meng Hui

      This is my situation:

       

      Group     Date          Amount

      1          1/11/2010     20

      1          2/12/2011     30

      2          1/14/2009     12

      2          3/17/2010     34

      2          4/1/2012       12

       

       

      I want to display the latest date for each group, then I want the amount to be displayed according to the latest date. This is the result I want:

      Group     Max(Date)       Amount

      1          2/12/2011          30

      2          4/1/2012            12

       

      However, tableau show me this result:

      Group     Max(Date)   Sum(Amount)

      1          2/12/2011     50

      2          4/1/2012       58

       

      I have tried to apply the attribute function to the measure but it does not work. I tried to change the measure to dimension also did not work. Any suggestions.

       

      Thanks.

        • 1. Re: Display corresponding measure value for latest date
          Joe Mako

          There are actually many methods and variations to achieve what you are looking for, including custom SQL, table calculations, and my preferred for this exact situation, self data blend.

           

          Bear in mind, that if you want anything in addition to what you have described here, the approach may need to be altered to fit your exact situation and desired final display. There are many factors to be aware of, and you have only supplied the minimum of details.

           

          I would not initially recommend the table calculations in version 7 (unless there were other constraints) because you are dealing with an exact date, and Tableau version 7 will HEAVILY pad your data when the compute using is an exact date, and the custom SQL may add slowness if you are dealing with a live connection.

           

          1. duplicate your data data source so you have a "(copy)"

          2. from the Data menu, Edit Relationships, and have a Custom with just "Group" kept

          3. bring out your fields of interest from the primary.

          4. in the primary data source (one with blue checkmark), create a calc field like:

           

          IF MAX([Date])=MAX([Data Source (copy)].[Date]) THEN 1 END

           

          5. place this calc field on the Filter shelf, and accept the default, keeping only the range from 1 to 1

           

          This is done in the attached.

          • 2. Re: Display corresponding measure value for latest date
            Meng Hui

            Thanks for your help. It's helpful.