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

# Calculated Field that is max date

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 Date Calculated Field Jan-12 Jun-12 Feb-12 Jun-12 Mar-12 Jun-12 Apr-12 Jun-12 May-12 Jun-12 Jun-12 Jun-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 Date Calculated Field Jan-12 Mar-12 Feb-12 Mar-12 Mar-12 Mar-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

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

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

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

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

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 Date Max Sale Date Days since Max Sale Date Jan-12 Dec-12 335 Feb-12 Dec-12 304 Mar-12 Dec-12 275 Apr-12 Dec-12 244 May-12 Dec-12 214 Jun-12 Dec-12 183 Jul-12 Dec-12 153 Aug-12 Dec-12 122 Sep-12 Dec-12 91 Oct-12 Dec-12 61 Nov-12 Dec-12 30 Dec-12 Dec-12 0

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

 Sale Date Max Sale Date Days since Max Sale Date Jan-12 Jun-12 152 Feb-12 Jun-12 121 Mar-12 Jun-12 92 Apr-12 Jun-12 61 May-12 Jun-12 31 Jun-12 Jun-12 0
1 of 1 people found this helpful
• ###### 6. Re: Calculated Field that is max date

Anyone have any thoughts?

• ###### 7. Re: Calculated Field that is max date

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