7 Replies Latest reply on Aug 29, 2016 7:48 AM by fei.yang.1

# how to match the multiyear treandline headers to the single year bar chart

Attached is a workbook of my problems in a simplified version:

There are multi year student absence data.

In the bar chart worksheet, it only shows absence data for ethnicity groups of that particular year (2014) and district (0130).

In the trendline worksheet, it shows trend of 10 years data for each ethnicity groups of that particular district 0130. However ,there are more ethnicity in header compared to the barchart because it shows all ethnicity groups as long as it showed up in one of the years.

Now my question is how to let the trendline only show the ethnicity groups same as the bar chart? For example, let it only shows the trendlines for ethnicity groups 2,3,7, rather than 2,3,4,6,7? This will be dynamically changing as filter changes.

Thanks.

• ###### 1. Re: how to match the multiyear treandline headers to the single year bar chart

Interesting question! We can't use a filter on both vizzes because, even though the bar needs to show data for only one year, the trend line needs to show data for several. As such, we may need to use a parameter and a couple of calculated fields to do the trick.See the workbook attached, in particular these 2 calculations:

- [Year Filter]

- [Trend Filter]

• ###### 2. Re: how to match the multiyear treandline headers to the single year bar chart

Thank you so much! I might have some more questions for you soon.

• ###### 3. Re: how to match the multiyear treandline headers to the single year bar chart

Hi Santiago,

As I mentioned there is more question related to my previous post, now if I look at the district level, since there are ~150 districts every year. If I only want to show 20 districts every year using the row index calculation (INDEX()), why it only works with bar chart, but the trendline would have random 20+ rows? New workbook attached in the original post with the "district--" as prefix. Thank you very much.

• ###### 4. Re: how to match the multiyear treandline headers to the single year bar chart

No worries. I can't see the workbook right now, unfortunately. However, as this is another question, might be a good idea to open a new thread and attach the workbook with the new problem there!

• ###### 5. Re: how to match the multiyear treandline headers to the single year bar chart

It is the extension of the original question just adding a row index. I can see the two attachments up there, if you still cannot see, please let me know and I will post as a new question. Thanks.

• ###### 6. Re: how to match the multiyear treandline headers to the single year bar chart

Here's a try with Rank_Unique instead. This will also let you show districts with more students, rather than just a random list. The problem with INDEX is that it's taking into consideration the District ID from the secondary data source and Year from the primary, this is mixing up the results.

The best solution actually will be to have both tables as part of the same data source connection, if we do that we don't have to worry about blending at all. The good news are, it's possible to join tables from different data sources in Tableau 10.0! Yet another reason to upgrade.

• ###### 7. Re: how to match the multiyear treandline headers to the single year bar chart

Hi Santiago,

Thanks for your reply. The Rank_Unique still shows the same problem, if the top_rank is changed from 15 to 20, then it starts having problem with the trendline, it shows 20+ lines other than 20 for different years, e.g. 2014 there are 21 and 2013 there are 22 etc. Because they are showing all districts that have data in the past years, however in the bar chart it only shows exact 20 district which has data in that particular year.

We have to use multiple data source because the real problem is more complicated than this and some of the rate calculations requires multiple data sources. Hopefully our organization will renew service thus we can get Tableau 10. Thanks!