2 of 2 people found this helpful
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.
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.
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.
"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.
Sale Example.twbx.zip 17.6 KB
1 of 1 people found this helpful
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
Anyone have any thoughts?
1 of 1 people found this helpful
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.