forums

Use these forums to ask questions and discuss Tableau.

Filter on maximum value

Posted October 14th, 2008 by acotgreave
in Tableau Desktop Basics

Hi,
I have a chart which shows student grades according to ethnicity values.

I have a filter to show only the current year's data. "Year" is a Dimension field. It is not a date - it is just the year, eg 2006, 2007, 2008.

I want the chart to show only the most recent year (ie the maximum value of Year). How can I set up a filter on the "Year" field such that it only shows the most recent data in the chart. The data source that the file connects to will have new years added to it; currently I have manually filtered out all but 2008, but as the dataset grows next year, I don't want to have to manually change it to show just 2009.

Hope that makes sense!

Andy

Add new comment

Comments

On October 14th, 2008 James Baker said:

Open up your Year Filter and go to the "Top" tab. Try "Top 1 by Year Maximum". Does that do what you want? (It should work for either string or numeric years.)

reply
On October 23rd, 2008 acotgreave said:

That works a treat. Thanks
Andy

reply
On December 11th, 2008 Ian Waring said:

I have a similar problem that this doesn't appear to fix. I have a list of quote references (text) in a SQLserver table, and a separate field with the quote version (integer). I want to pick just the quote lines of the last version of each quote (ie: highest version number for any quote reference), whatever it is.

Any ideas how I could achieve that - short of having to escape out into Excel?

reply
On December 12th, 2008 James Baker said:

Ian,

It can be done, but it's way way harder than it should be. The workbook I'm attaching uses 3 advanced tricks to do what you're looking for. The method was developed by Ross and Raif for this blog post: http://www.tableausoftware.com/blog/summerolympics

I can explain the tricks and why they work but... I don't think it's worth it in this case. :-p If you can replicate this exactly, great. If not, it basically can't be done.

  1. quote (copy), version, 1 to Rows
  2. "quote & version" to Level of Detail
  3. change SUM(1) to MIN(1)
  4. Add table calc "Running Total, Sum, of quote&version" to MIN(1)
  5. double-click TC MIN(1) (adds to filter shelf, no filtering yet)
  6. Ctrl-drag (copy) TC MIN(1) to Level of Detail
  7. change TC MIN(1) in Rows to discrete
  8. sort "quote & version" (from LoD) to Descending by Field:version Maximum
  9. Right-click-drag "text" onto the Text shelf - select MIN(text)
  10. now edit the MIN(1) filter down to just the value 1
  11. turn off "show header" of the MIN(1) on the shelf

Whew! Some day we may add support for doing this "limit within context" explicitly.

AttachmentSize
quote_version.twbx52.13 KB
reply
On December 19th, 2008 Ian Waring said:

Thanks James.

The bit i'm stuck on is trying to create a set called "Quote & Version" based on a SQLserver string field and integer respectively. Whenever I try, as soon as I go to level of detail in your instructions, Tableau goes out to lunch for over 2 hours (okay, it's a 2 million record SQLserver database).

Do I need to collect a set based on the contents of the two fields? Apologies if this is a real basic question - none of my mouse gymnastics seems to get me close to the set definition you have in the sample sheet!

Ian W.

reply
On December 19th, 2008 James Baker said:

Ah yes, creating the set. In my example, "quote" was an integer ID - presumably you have some unique identifier for each defined quote (that's not the actual text which of course changes with each version)?

reply
On December 20th, 2008 Ian Waring said:

"Quote Header Number" is a fixed eight digit numeric albeit held as a string in our SQLserver quotes database. The version (field called "Revision") is an integer that normal steps up from 1 (and I don't recall it ever going much higher than version 10). All quote versions get left in the database, so the task is to just extract the highest "revision" of each.

At the moment, i'm having to escape out to Excel, pivoting quote header plus max(revision), then in the original data, doing a vlookup into the pivot table to compare each quote line's revision number against the one for that quote number in the pivot; if match, mark it as "latest", else "older". Then filtering just the "latest" quote lines out.

Would obviously love it if Tableau could do the gymnastics for me :-)

reply
Syndicate content Subscribe to the comments on "Filter on maximum value"