4 Replies Latest reply on Jul 9, 2018 12:40 PM by Nikhil Tawakley

# Top N- Filter & Plot

Suppose one has the following table that tracks the size of a software project over time. Now lets say we want to plot the end-of-day size vs date. How does one get Tableau to pick the max(Version) each day on the plot? (A simple plot between Business Date vs Size would add up the sizes of each version.)

VERSION_SIZE_TABLE:

 Date Version Size (MB) 09-Jul-18 0 88 09-Jul-18 1 54 10-Jul-18 2 15 11-Jul-18 3 61 11-Jul-18 4 6 11-Jul-18 5 50 12-Jul-18 6 14 13-Jul-18 7 33 13-Jul-18 8 13 13-Jul-18 9 64 13-Jul-18 10 50 13-Jul-18 11 38 14-Jul-18 12 86 15-Jul-18 13 60 16-Jul-18 14 28 17-Jul-18 15 33 18-Jul-18 16 94 19-Jul-18 17 76 20-Jul-18 18 15

One way to approach this would be to write SQL query to create an intermediate table that only holds the latest version per day (using an outer join). However, my data set is huge and while the following query works, it's painfully slow.

select *

from {oj VERSION_SIZE_TABLE s1 right outer join

(select max (Version) AS Version, Date from VERSION_SIZE_TABLE group by Date) s2

ON s1.Version=s2.Version and s1.Date=s2.Date}

Tableau Version: 10.0.0

Thanks,

NT

• ###### 1. Re: Top N- Filter & Plot

Hi Nikhil,

You can do this by using an LOD calculation
{Fixed Date: Max([Version])}

Hope this helps,

Soham

1 of 1 people found this helpful
• ###### 2. Re: Top N- Filter & Plot

Thanks for the quick reply Soham!

The LOD calculation {Fixed Date: Max([Version])} returns the highest Version # for any given day; now how does get the size that this Version corresponds to on a given day? I need to plot latest version size vs date.

My apologies in advance for the rookie question . Still very new to Tableau...

Thanks again,

-NT

• ###### 3. Re: Top N- Filter & Plot

Do you want something like this?

Total size of the software at end of every day?

• ###### 4. Re: Top N- Filter & Plot

I was expecting the table below which lists the size of the highest version each day. I want to filter out/drop the other records.

 Date Version Size (MB) 09-Jul-18 1 54 10-Jul-18 2 15 11-Jul-18 5 50 12-Jul-18 6 14 13-Jul-18 11 38 14-Jul-18 12 86 15-Jul-18 13 60 16-Jul-18 14 28 17-Jul-18 15 33 18-Jul-18 16 94 19-Jul-18 17 76 20-Jul-18 18 15