You should be able to accomplish this with a little trickery! You do not need to create a new dataset, you can have Tableau use your dataset twice, sort of like a self join in SQL terms.
- Your Tableau data connection has a dataset called "MonthlyJob" and the date the job was run is a date column called "RunDate"
- You have already built a Viz. A good test would be a text table with RunDate dimension and Sum(Number of Records) Measure
Steps to take
- Click on Data / MonthlyJob / Duplicate
- You will now see a new dataset called MonthlyJob (copy)
- Create a new Calculated Field in the MonthlyJob dataset called "SelectMaxDate"
- Formula to use is "attr([RunDate]) = total(max([MonthlyJob (copy)].[RunDate]))
- This will be a boolean Measure field, with the "T | F" Icon
- Drag and drop the SelectMaxDate field into Filters
- A dialog box will pop up with the two possible choices - True and False
- Check the True option and your Viz will adjust to filter out all but the latest RunDate values
- You may need to adjust the "Compute Using" property, depending on your final viz characteristics, but I do not think it will matter
- You should definitely validate that the duplicated data set did not result in a cartesean product. In my test data I have multiple records per day and this method did not result in any duplicated data
- Depending on the size of your data, I do not know if you will hit performance problems
Hope this helps!
Thanks for your help!
As long as I have a 2nd dataset, might as well just get the max from a special query, instead of getting every row, then doing a max. That will eliminate the extract overhead on the database.
Moving thru the rest of your solution...
Question about your solution:
attr([RunDate]) = total(max([MonthlyJob (copy)].[RunDate]))
Is this setting the calculated field? Or is it returning a boolean based on whether the Run date is equal to the max run date?
It will return a boolean.
That being said, this solution would only work if you have the date on your vizualization (just realized that). So, it will not work in all cases.
I think your best bet is to create a second data set that just has the max date in it and then in your data model, join the 2 tables which will filter the data for you.
Sorry for the partial solution there :/
Think I got it. You put me on the right track.
Did it all in one datasource.
Added a custom query which pulls in the max date tied to some common field. Join to main table based on common field. Now max date (most recent run date is in every row returned)
Added a calculated field, test date against the max date.
Remove the old filter/use calculated field as the filter
It is great if you can manipulate the input data like that before hitting Tableau. Sometimes we get so caught up in making the tool do everything (especially in places where getting DB changes is like moving a mountain)
OK, so, this was gnawing at me as "there has got to be a simpler way"...
And there is!
You can create a set that will do this, no need to manipulate your incoming dataset with the max date or anything like that.
- Right Click on your date field
- Choose Create Set
- Give it an appropriate name
- Click on Top tab
- Click on "By Field" button
- Change Top 10 to Top 1
- Change Number of Records to your date field
- Change Sum to Maximum
- Click OK
- Drag your set onto the filter shelf and you are good to go