5 Replies Latest reply on Feb 3, 2012 7:56 AM by Chris Jokel

    Using MAX([Date]) instead of TODAY()

    Chris Jokel

      I have attached my workbook for reference.

       

      For the calculated field "Date Breakdown - Nov" I am currently using the function TODAY().

      Long story short, I don't want to reference a dynamic date and would prefer to let the data decide what the most recent date is.

       

      How can I replace TODAY() with MAX([Date]) or some equivalent?

      I have seen examples using TOTAL(MAX([Date])) but I keep getting the "Cannot mix aggregate and non-aggregate arguments to function" error regardless of what I try.

        • 1. Re: Using MAX([Date]) instead of TODAY()
          Alex Kerin

          Okay, see attached. I created a new Date Breakdown that looks for the window max:

           

          IF DATEDIFF('month',attr([Date]),window_max(attr([Date]))) =0 THEN STR(DATENAME('month',attr([Date]))+' '+DATENAME('year',attr([Date]))) ELSEIF

          DATEDIFF('month',attr([Date]),window_max(attr([Date])))=1 THEN "1 Month Prior" ELSEIF

          DATEDIFF('month',attr([Date]),window_max(attr([Date]))) =3 THEN "3 Months Prior" ELSEIF

          DATEDIFF('month',attr([Date]),window_max(attr([Date])))=6 THEN "6 Months Prior" ELSEIF

          DATEDIFF('month',attr([Date]),window_max(attr([Date]))) =12 THEN "12 Months Prior" ELSEIF

          DATEDIFF('month',attr([Date]),window_max(attr([Date]))) =36 THEN "36 Months Prior" ELSEIF

          DATEDIFF('month',attr([Date]),window_max(attr([Date]))) =60 THEN "60 Months Prior" ELSE

          "X"

          END

           

          How you then set up the partitioning when you move it to the shelf determines how (and if) it works. The way I have it set up is that it looks for the max date for every property type. This causes some issues - if the property type doesn't have an entry for the most recent month, you end up with a blank. You could change the partitioning (as per 2nd new sheet) with the same calc so that it looks for the most recent date for that property type. This is fine, but you end up with 3 months as the most recent.

           

          Finally, the last new sheet has a different calc, where it's labelled as most recent, not the month name. There are still blanks - maybe because there isn't data for 3 months ago?

           

          I did change the equation because I think the datediff for 1 month prior is 1, not 2, and so on.

          1 of 1 people found this helpful
          • 2. Re: Using MAX([Date]) instead of TODAY()
            Chris Jokel

            Hi Alex,

             

            Thanks for responding.

             

            It is strange that blanks should appear, because if you go to "View Data" on the extract, the data set is complete for all property types and dates.  I myself am not sure why some of the values would be omitted from the table if they are not missing from the data.

            • 3. Re: Using MAX([Date]) instead of TODAY()
              Alex Kerin

              Hmm, redone, the partitioning here assumes that the max date is the same for all properties/regions (i.e. one max date for the entire dataset). I'm at a bit of loss to explain why there was data missing. I did add date to the columns, then hid its header.

              1 of 1 people found this helpful
              • 4. Re: Using MAX([Date]) instead of TODAY()
                Chris Jokel

                Thanks Alex, that seems to have solved the problem of the missing data.

                 

                The only issue I have left is that originally I was using the dimension "HPI or Benchmark revised" instead of the measure "HPI or Benchmark" in the Text shelf for my own special formatting purposes.

                 

                In the view Item1 I could interchange these no problem, but  this is not possible with the new calculation.  My calculated field is essentially just a string based on the measure values with an added decimal or dollar sign.  When I switch them, Tableau reads "Computing View Layout" and then expands the table to include all sorts of values.

                 

                Sorry, I'm still relatively a beginner to Tableau so I'm not sure why a string conversion would have this effect.

                • 5. Re: Using MAX([Date]) instead of TODAY()
                  Chris Jokel

                  It turns out that if I swap in "HPI or Benchmark revised" and then change this to an Attribute on the Text shelf, it will work and Tableau will not display all of the extra columns.

                   

                  Thanks very much for the help Alex.