7 Replies Latest reply on Jun 25, 2013 12:37 PM by Mark Holtz

    Calculated Field that is max date

    Jake Utzig

      I cannot figure out how to create a calculated field that is the maximum date of another date field.  For example, a column in my data source is SaleDate, with dates ranging from Jan-12 through Jun-12.  I want to create a calculated field that would show the max date of this column for each record in the table, like so:

       

      Sale DateCalculated Field
      Jan-12Jun-12
      Feb-12Jun-12
      Mar-12Jun-12
      Apr-12Jun-12
      May-12Jun-12
      Jun-12Jun-12

       

       

      When I apply a quick filter to the sale date (e.g. limiting sale date to <= Mar-12), this calculated field should update as well:

       

      Sale DateCalculated Field
      Jan-12Mar-12
      Feb-12Mar-12
      Mar-12Mar-12

       

      I have recently made the switch from Spotfire, and am having a hard time converting from Spotfire's "Over" statements to Tableau's equivalent.

       

      Any help would be much appreciated,

      Jake

        • 1. Re: Calculated Field that is max date
          Jake Utzig

          I know that this can be achieved by: TOTAL(MAX(SaleDate)).

           

          The problem that I am trying to solve is that this new expression cannot be used in combination with non-aggregate expressions.

           

          Ultimately, what I am trying to do is calculate the days since the most recent sale:  DateDiff('day',[SaleDate],TOTAL(MAX([SaleDate]))).

           

          One workaround is to add a MaxSaleDate column to the data source and pull this column in as well.  The problem here is that the field is not dynamic and will not update when a filter is applied to SaleDate.

           

           

          Any thoughts?

          2 of 2 people found this helpful
          • 2. Re: Calculated Field that is max date
            Noel Avison

            Why is just doing Max([SaleDate]) not working? Also you should probably be doing DateDiff('day', Max([SaleDate]),Now()) to find the difference between the current date and the max sale date.

            • 3. Re: Calculated Field that is max date
              Jake Utzig

              Thank you for your response, Noel.

               

              Unfortunately, I am not trying to find the difference between the current date and the max sale date.  Your recommendation would return the same value for every row, regardless of the sale date.  Also, I am trying to avoid using TODAY()/NOW() because:

              1) the most recent sale date is not always today

              2) today becomes irrelevant when a filter is applied to sale date

               

               

              Max([SaleDate]) works fine in itself, but it cannot be used in a calculated field that evaluates each row separately.

              • 4. Re: Re: Calculated Field that is max date
                Noel Avison

                You said:

                 

                "Ultimately, what I am trying to do is calculate the days since the most recent sale"

                 

                I am not sure how you would accomplish this without having the current date for reference. Is there a field in your data that has the current date already?

                 

                The Now() field is not telling you when you had a sale, it is giving you a reference point for comparison. For example if your most recent sale data was June 17, you Max(SaleDate) would be June 17. DateDiff('day', Max([SaleDate]),Now()) would return a value of 3 because today is June 20th. If you were to filter out June 17, Max(SaleDate) would then be the next highest date and Now() would stay the same so it would now compare those 2 dates instead.

                 

                I attached a workbook with that I think you are trying to do.

                1 of 1 people found this helpful
                • 5. Re: Calculated Field that is max date
                  Jake Utzig

                  I apologize for the confusion.  I am looking to calculate the days since the most recent sale for each sale.

                   

                  Here is what I am trying to do:

                  Sale DateMax Sale DateDays since Max Sale Date
                  Jan-12Dec-12335
                  Feb-12Dec-12304
                  Mar-12Dec-12275
                  Apr-12Dec-12244
                  May-12Dec-12214
                  Jun-12Dec-12183
                  Jul-12Dec-12153
                  Aug-12Dec-12122
                  Sep-12Dec-1291
                  Oct-12Dec-1261
                  Nov-12Dec-1230
                  Dec-12Dec-120

                   

                   

                  If a filter is applied to the Sale date, e.g. SaleDate <= 6/30/2012, then the table would adjust as follows:

                  Sale DateMax Sale DateDays since Max Sale Date
                  Jan-12Jun-12152
                  Feb-12Jun-12121
                  Mar-12Jun-1292
                  Apr-12Jun-1261
                  May-12Jun-1231
                  Jun-12Jun-120
                  1 of 1 people found this helpful
                  • 6. Re: Calculated Field that is max date
                    Jake Utzig

                    Anyone have any thoughts?

                    • 7. Re: Calculated Field that is max date
                      Mark Holtz

                      Hi Jake,

                       

                      You can use the ATTR() wrapper to convert a non-aggregated value (even a dimension) to an "aggregated" value, but that expression will always return one of two options:

                      1) if every value withiin the partition is the same, it will return that value

                      2) if there are more than one value within the partition, it will return *

                      It is handy for cases such as this where you want to compare a "global" max to a "local" max or to each record.

                       

                      To respond to Noel Avison's question: "Why is just doing Max([SaleDate]) not working?", that would be because MAX evaluates within each partition, and so when we group by sales month, it will return the max date found within each month. For example, if for sales month Jan-12, we have sales dates of 1/15/12, 1/20/12 and 1/25/12, it will return 1/25/12. But for the next partition of Feb-12, the max function will evaluate values like 2/15/12, 2/20/12 and 2/25/12 and choose 2/25/12.

                       

                      Back to Jake--in your example, you should be able to use something like TOTAL(MAX([Sales Date]) - ATTR([Sales Date]. The catch here is that if you truly have unique dates in your data (e.g., 1/15/12, 1/20/12, 1/25/12) then you will be getting the * if you aggregate to a month level.  To get around that, you would need to create a field to change all [Sales Date] values into their respective Sales Month. DATETRUNC('month',[Sales Date] should do the trick for that. Then you would group by the Sales Month and use the MAX and ATTR of that field instead of [Sales Date] If you still can't get it to work, post a packaged workbook and we should be able to get it working for you.

                      1 of 1 people found this helpful