-
1. Re: Show Only Values From Max Date in Dataset
Jonathan DrummeyAug 5, 2013 6:36 PM (in response to John Mogielnicki)
Hi,
See the attached for two options. One uses a table calc and requires Date to be in the view. The other is how I tend to do this sort of thing in Tableau, I use a subquery (in this case, I used Custom SQL) to generate another column for the latest date and then it's a simple Boolean evaluation.
Jonathan
-
Max Date jtd edit.twbx.zip 12.1 KB
-
-
2. Re: Show Only Values From Max Date in Dataset
John Mogielnicki Aug 6, 2013 10:19 AM (in response to Jonathan Drummey)Hi Jonathan,
Thank you for the solution. I'm glad I wasn't just missing an obvious answer.
Question for you: why do you tend to use the sub-query instead of the table calc? Is it for performance reasons? Seems like it's not a downside to have to keep date in the level of detail shelf.
Thanks,
John
-
3. Re: Re: Show Only Values From Max Date in Dataset
Jonathan DrummeyAug 6, 2013 2:17 PM (in response to John Mogielnicki)
1 of 1 people found this helpfulHi John,
Good question, here's my thinking:
- When using the table calc, Tableau is returning every date from the data source and computjng row-level and regular aggregate results for all the rows that are hidden by the table calc filter. This may not performant.
- Putting Date on the LoD increases the level of detail in the view. If you have other aggregates in the view, then they all need to take that into account, which can potentially mean a whole bunch of table calcs that have to be maintained.
Jonathan
-
4. Re: Re: Re: Show Only Values From Max Date in Dataset
John Mogielnicki Aug 6, 2013 5:14 PM (in response to Jonathan Drummey)1 of 1 people found this helpfulJonathan and Johan,
Thank you both for taking the time to respond to my question. I implemented your first solution (within Tableau) and it worked perfectly.
Until, that is, I needed to take it a step further (isn't that always the way?).
Now I need to use this max date not as a filter, but as an anchor for a calculated field that takes the value associated with the latest (max) date, and gives a percent change from the value associated with the second to last date. Using the dataset from my original post, this would mean the calculation should be (5/4)-1. I have done this in the past, and used a date parameter value as the anchor, which works out great, but I can't figure out how to make this work in this case.
Attached is a workbook with the example of how I thought I could get to what I need. Any further information would be really helpful.
If it turns out I need to use custom SQL to get what I need, that would be fine. But I would prefer an option within Tableau if that's possible.
Thank you,
John
-
5. Re: Re: Re: Re: Show Only Values From Max Date in Dataset
Jonathan DrummeyAug 7, 2013 5:08 AM (in response to John Mogielnicki)
2 of 2 people found this helpfulHi John,
I'm thinking that the direction you were going in is more complicated than necessary since it seems like all you're looking for is the % diff from prior for the latest date?
There are a number of ways to do this, I set up four options in the attached:
option 1 - using the Quick Table calc % diff from prior, and the TC filter for the latest date.
option 2 - same quick table calc, just using a LAST()==0 filter
option 3 - using the custom SQL data and a filter to just get the last two dates (so Tableau doesn't have to return all the dates from the data source), then is set up like option 1.
option 4 - using the custom SQL data and a row-level calc to do the % diff for the last two dates, does not require Date to be in the view.
Jonathan
-
6. Re: Re: Re: Re: Show Only Values From Max Date in Dataset
John Mogielnicki Aug 7, 2013 8:35 AM (in response to Jonathan Drummey)Thanks Jonathan for the comprehensive and detailed response.
The answer turned out to be so simple. I didn't even try it, because I thought that if the data was filtered out there would be no way to use it in a table calculation.
Would you be willing to help me understand how this works? I was under the impression that filters came first, and then table calcs could only use data that was not filtered out of the view.
Thanks,
John
-
7. Re: Re: Re: Re: Show Only Values From Max Date in Dataset
Jonathan DrummeyAug 7, 2013 9:30 AM (in response to John Mogielnicki)
2 of 2 people found this helpfulIt's true that most filters come first, but not filters based on table calculations: Here's a simplified order of operations:
(all three of these filters are processed in the data source)
Data Source Filters
Context Filters
Regular Filters
Computation of row level calculations
Computation of aggregate calculations
(the rest of these happen inside in Tableau)
Data densification
Data blending
Compute table calculations
Table calc filters
Filters on generated lat/long
Hiding
Within the three types of filters that happen in the data source, there's a sub-ordering:
Top & Conditional Filters
General & Wildcard Filters, and Range filters on dimensions
Filters on regular aggregates
So filters on (almost***) anything that is not a table calculation are applied within the query to the datasource, whereas filters on table calcs are applied after most all computation is complete. The almost*** is because there are a couple of situations with data blending where Tableau can do its own computation and and application of filters on aggregates (and maybe row-level, I'd have to go back to my notes to check).
The best post on this is http://community.tableau.com/message/139603#139603
Does this help?
Jonathan
-
8. Re: Re: Re: Re: Show Only Values From Max Date in Dataset
John Mogielnicki Aug 7, 2013 5:19 PM (in response to Jonathan Drummey)Hi Jonathan,
You have been incredibly helpful. Thank you again for taking the time to provide such detailed responses.
John
-
9. Re: Re: Re: Re: Show Only Values From Max Date in Dataset
Jonathan DrummeyAug 8, 2013 9:32 AM (in response to John Mogielnicki)
You're welcome! -
10. Re: Show Only Values From Max Date in Dataset
radhika.sriramoju Jan 26, 2016 1:54 PM (in response to John Mogielnicki)Hi
I am trying to download the file .. but cannot .. it is deleted or something ?
Thank you!
-
11. Re: Show Only Values From Max Date in Dataset
radhika.sriramoju Jan 26, 2016 2:02 PM (in response to Jonathan Drummey)Hi
Can you please repost this file as an packaged work book I am unable to open it
Thanks
-
12. Re: Show Only Values From Max Date in Dataset
Bernardo Lares Dec 7, 2016 9:29 AM (in response to John Mogielnicki)I have almost the same problem but I can't seem to find the solution. I need to identify which one is the latest Activity for each ID Activity.
This is an example of what I have:
This is what I need:
I know that if I create a calculated field with the max date for each ID Activity I can filter them and that's it, but how can I do that. I've tried several things but none useful to mention.
I hope you guys can help me! Barry PlumThe specified item was not found.Amy SchneiderThe specified item was not found.Erin Easter Elissa Fink
Thanks very much.
-
13. Re: Show Only Values From Max Date in Dataset
Ben Kiviti Oct 23, 2017 5:34 AM (in response to Bernardo Lares)have you found a solution here ?
I have tried
If [date]={ fixed [id_activity]: max([date])} then true
else false
end
however didn't get to the req result.
-
14. Re: Show Only Values From Max Date in Dataset
sai ja Jan 17, 2018 10:14 AM (in response to Bernardo Lares)Hi,
Use this,
{ FIXED [Activity]: Max([Date])}