See the attached workbook & jpgs. There are a couple of ways to do this.
All I did here was change the default format of the calc'd date field you created from "Automatic" to "d/m/yy" I did this in the measure box.
See the screen shots. Start shows you the formatting in your .tbw file. After shows what I changed the formatting to.
Instead of creating a duplicate date field, you could also do this by using the original date dimension, setting the default format there and putting it on the detail shelf and changing it to a Max measure.
sorry. maybe i wasn't clear. i'm not concerned here about the formatting of the date. i want the title to indicate that the last date in all the data, not just the filtered data, is July 5th.
Let me take another look. My bad. I read it too quickly and didn't realize you want to see 7/5 not 7/3.
Sorry, I don't have a direct answer for you.
Your data set has sales for apples on two days 7/1 and 7/5. However, the worksheet you show only has data for oranges and oranges only have sales for two of those days; on 7/2/13 and 7/3/13.
Therefore to my knowledge the max date you can show on that work sheet is 7/3/13 b/c that's the only date in the data that's being shown. I played around with a few custom calcs, but couldn't get them to work. Perhaps others here, can point you to a custom calc that will display the date you want.
Alternatively, you can create a separate worksheet that shows the max date in the data and combine that into a dash board. For me, I like to make it clear if the data I'm showing indicates that apples and oranges didn't sell on every day of the month. In the analysis I do that can lead to an insight that would be otherwise disguised. But I don't know if that applies to your situation.
We faced same issue and checked with Tableau consultant. He recommended adding additional column in the data source named MAX_DATE that will have the max date for every record in the data source. We implemented this approach on DB side and all works fine.
This does not look nice solution but it works perfect!
Interesting approach. I want to make sure I understand it so I can consider it as a solution.
Are you doing this in a dbase or .xls or some other data source? Are you doing it manually or automatically?
Do you mean that every row in the column Max-Date has the same date in it? And that you overwrite and replace the date in that column in every row, every time the max date in the data set is higher than the last time?
Here are a couple of other options that will allow you to get to the max date in the data without constantly updating the source (though that might be a preferred approach in some circumstances).
- Use a table calc filter. Table calculations are preformed post-query, so the data exists in the data engine to get the max, but the table calc will effectively hide other values.
- Create a copy of the data source and use data blending to get the maximum date in the data. I created a calculated dummy field to link the filtered data in the primary source to all the data in the secondary source.
I've attached your workbook with a couple of tabs to demonstrate both approaches. I'd be happy to answer any questions you might have!
max date sample workook -jm.twbx 11.9 KB
We are doing it in database and automatically.
Yes for every row, Max-Date column has same value. We overwrite it each time data is refreshed.
viraj gholap : Thanks for providing the detail. It's helpful to know.
Joshua Milligan: Thanks for posting the work book. This is Tim's question so ultimately he has to decide if any of the options you present will work for him. your table calc solution is exactly what I was envisioning in my comment that perhaps others can propose one as a solution. I'm going to use that model for some other issues I encounter.
I get that you've effectively recreated the Dimension product name as a Measure that displays the text of the Product dimension. And hat b/c it's a Table Calc, when we use it as a filter on product types it sees the data (and dates) in the data set and not just the dates for what's displayed in the view?
Yet, I have questions about how it works that will help me understand how to apply this type of solution in other situations.
Calc name (measure): Product (Table Calc Filter)
Formula: LOOKUP(ATTR([Product]), 0)
When I look at the calc, I see:
- It works on a Dimension in the data named Product;
- The table calc is a measure, not a Dimension;
- It displays text - the names of the Products - just like original dimension, not numbers.
- You use the LookUp function set w/ a 0 offset, and the ATTR function.
I'm going to play around with the formula to see if I can figure out how it works. In the meantime, am I correct in understanding the following?
- we need to use the ATTR function as a workaround to turn a dimension into a calculated measure. And the function treats the text behind the scenes in the Product Dimension as a Number that will display as text,
- we use the LookUp function (with the offset = 0 as a kind of placeholder) so the formula displays the text and does so w/o looking back or forward.
What you've stated is essentially correct. When dimensions and measures are used in a view (on Rows, Columns, Color, Text, etc...) they generate a query to the underlying data source. When the results are returned to Tableau, they are returned as a data table containing just enough rows for Tableau to render the viz (so, for example, a bar chart with 5 bars may result in a query that aggregates millions of rows into a table with 5 rows of aggregate data).
Table calculations are performed on the resulting data table. The LOOKUP function allows you to lookup values in the data table at various offsets (the 0 offset does indeed mean that it is not looking backward or forward). Table calculations always operate on aggregates. That's why ATTR is used. It treats the dimension as an aggregate.
Using that calculation as a filter still allows unfiltered data from the source to be retrieved into the data engine and thus other calculations can reference values that are not shown.
Thanks everybody, but particularly to Josh & Viraj.
In this case I went with adding a max_date column to the datasource, which is a view built in Oracle. The query from Tableau is just a 'select * from ...' on a several million row database, so this was the most straightforward solution.
Josh: I liked both of yours, particularly the data blending option. I will almost certainly use one of your proposed solutions in the future. You even lead me another way of linking....
Create two data connections. Instead of Connection 2 being a duplicate of Connection 1 it is just 'select max(date) from...' and then add the dummy link variable to each connection. Blend and there you have it!