5 Replies Latest reply on Jun 7, 2016 10:10 AM by Ivan Young

# Filter Selection combined with Last/Max

Hi,

somehow I am stuck with something that should be rather simple - none of the answers I found (parameter + calculated field, LOD, Last(), Rank(), index(), table calc, etc) produces the desired outcome ....

The dataset comprises (among many others to be ignored for this question) 5 columns:

- clip: video clip id

- item first: item shown

- item next: item shown after item first

- duration (s): time passed between two items (in seconds)

- Summed duration: running total of duration (in seconds)

Task: select via a filter max time duration and based on this filter selection the item shown last.

Desired Result (Examples):

- Duration Selection =   "5s" :  clip A | AA , clip b | AA.

- Duration Selection = "20s" :  clip A | DD, clip b | CC.

Attached are a dummy data set and a tableau workbook with one not perfect solution just to give the idea.

Thanks a milion to anyone who cuts through my Gordian knot and provides the (probably simple) solution!!

• ###### 1. Re: Filter Selection combined with Last/Max

Hi Lucie,

I was unable to open your workbook but I've created a solution which solves for the two test cases you provided using the Excel.  What I did was lookup -1 the last SummedDuration substituting a 0 for the first row as there is nothing to lookup, then created a filter that returns true if the param value falls between SummedDuration and the and the previous rows summed duration.  The solution is pretty simple but it took me a few swings before coming up with a hopefully workable solution.  Let me know if you have any questions.

Regards,
Ivan

• ###### 2. Re: Filter Selection combined with Last/Max

Hi Ivan,

thanks a lot for the answer. Unfortunately the lookup solution only works as long as you keep the view a list. But one cannot continue to analyse this further.

For example the question: how many clips have as a last product DD or CC after 15 seconds. If one adds a "count" then this lookup-solution breaks.

Sorry that you could not open my File - I use version 9.3

Still, thanks a lot for your thoughts.

Cheers, Lucie

• ###### 3. Re: Filter Selection combined with Last/Max

Hi Lucie,

Sorry it didn't work for you.  Do you have access to SQL to restructure the data?  You could structure your data so it would be pretty easy to get what you're looking for.

Regards,

Ivan

• ###### 4. Re: Filter Selection combined with Last/Max

Hi Ivan,

well, I first wanted to figure out whether Tableau can do this (ideally in a simple way) before I ask for changing the automised data extraction.

Anyway, thanks a lot for thinking about my challenge!

Cheers, Lucie

• ###### 5. Re: Filter Selection combined with Last/Max

Hi Lucie,

I realized you could basically mimic the SQL using data blending on a duplicate data source.  Check out the attached and let me know if you have any questions.

Regards,

Ivan

I duplicated all the rows to get counts above 1.