5 Replies Latest reply on Jan 5, 2019 1:07 PM by Ken Flerlage

# Top N Filter with Last Date's Value

Hello,

I have a dataset of Age Dependency Ratio of Old People to the Working People. I am trying to show a line chart of Top 10 Countries across the years. My year dimension ranges from 1960 to 2017.

Here is the screenshot of Tableau sheet showing Top 10 Countries across 1960-2017. But, I want to create the line chart not according to overall years' Top 10 countries. Instead, I want to create it with Top 10 countries for 2017. Please look at the second picture.

Here are the Top 10 countries in 2017. My purpose is to create the line chart of those countries ranging from 1960 to 2017 like above.

• ###### 1. Re: Top N Filter with Last Date's Value

Create a calculated field that will give you just the 2017 ratio.

2017 Ratio

// Get the ratio for 2017 only.

IF YEAR([Year])=2017 THEN

[Age Dependency Ratio]

END

Then use that in your top 10 filter.

See attached.

1 of 1 people found this helpful
• ###### 2. Re: Top N Filter with Last Date's Value

Or, if you want it to always show the latest year in the data set, you could do the calculated field like this:

// Get the ratio for the latest year only.

IF YEAR([Year])={FIXED : MAX(YEAR([Year]))} THEN

[Age Dependency Ratio]

END

You'll probably want to add the Year filter to context though, so that it applies before the LOD above which gets the latest year.

1 of 1 people found this helpful
• ###### 3. Re: Top N Filter with Last Date's Value

Thank you for your quick answer Mr. Ken. It really helped me.

There is also "World" ratio in the country name dimension. I also want to show the 'World' line chart in the same Top 10 countries chart.

I really tried to solve it. But I couldn't make the filters work.

• ###### 4. Re: Top N Filter with Last Date's Value

Sorry. Mr Ken

I just added to your calculated field OR country name = 'World'

and it worked.

I really thanked you again.

Best regards

Kursat

• ###### 5. Re: Top N Filter with Last Date's Value

Perfect!