6 Replies Latest reply on Feb 17, 2016 10:08 AM by Greg von dem Bach

# 3-year filter, index by latest year...

Hi everyone,

I have a rather simply report that lists the record counts by Category for each of the last 3 years.

What I'd like to do is create an index on one of the 3 years and filter the top 2 Categories based upon the index but display all 3 years for each qualifying Category.

An example:

If my current report looks like this:

Category A

2014     100

2015     85

2016     225

Category B

2014     50

2015     99

2016     150

Category C

2014     450

2015     125

2016     750

I want to index by year 2016, select the top 2 in that index and display all years for the qualifying Categories (C & A):

Category C

2014     450

2015     125

2016     750

Category A

2014     100

2015     85

2016     225

Is it possible to index on one filter value and use that to limit one dimension (category) while not limiting another dimension (year)?

• ###### 1. Re: 3-year filter, index by latest year...

Hey Greg,

You can indeed do this. I've attached a sample workbook - but essentially what I did is calculate the 2014 sales in Superstore with an LoD calc fixed at the "Category" level. (Superstore doesn't have 2016)

Then, I used an advanced index at the "Category" level for the filter.

• ###### 2. Re: 3-year filter, index by latest year...

Hi Derrick,

So I create a calculated field based upon the 2014 year count and placing it on the row shelf adds it to every row.  Very clever.  BTW, it looks like you made a typo substituting "2016" for "2014" in the calc field and column header.

The only thing I can't figure out is how you based the index upon the 2014 Sales (2016 Sales) calculated field.  I have the Index "compute using" field set to Category so it gives the same Index value to every year within the Category, which is what I need.  But I can't seem to get the Index to use the value in the 2014 Sales (2016 Sales) calculated field in descending order.

How do I tell the Index formula to use a specific measure for the indexing values?

• ###### 3. Re: 3-year filter, index by latest year...

Hi Derrick,

I've attached my workbook so you can see what I'm working with.

As you can see, your calculated field based upon Year gives each Year within Category the record count for 2015.  Which is perfect.

Now I need to create the Index based upon that 2015 Count calculated field in descending order.  So 12 = Index 1, 6 = Index 2, 4 = Index 3.

I'm pretty sure I can figure out the filter based upon the Index, I just need a hint on how to base the Index upon that 2015 Count value.

• ###### 4. Re: 3-year filter, index by latest year...

Ah, I see what you want. Instead of the straight INDEX field I'm using, use something like this.

Then you can target the specific field DESC.

RANK(ATTR([2015 Count]), 'desc')

1 of 1 people found this helpful
• ###### 5. Re: 3-year filter, index by latest year...

Hi Derrick,

Thank you very much for your help.  That gave me enough to get it working.

I used a dense_rank to get continuous rankings:  rank_dense(attr([2015 Count]),'Desc')

I also changed it to compute using Table(Down).

I'm new to Tableau and what initially seems rather intuitive actually requires quite a bit of tweaking.  Eventually I'm hoping all of this becomes engrained so I don't feel so clueless.

Again, thank you very much for your help.