1 2 Previous Next 29 Replies Latest reply on Oct 15, 2013 7:05 AM by Jonathan Drummey

# How to get top 3 sales country based on max week in line trend

Hi All,

I am facing issue to get top countries based on maximum week in a line trend chart. Ex. I have data for wk 32, 33, 34 and need to get top 3 sales countries based on maximum week and those top 3 countries need to display to the entire weeks. Here, wk 34 is maximum week and CN, FR, UAE are top 3 countries and these 3 will display as 3 lines in the chart for entire weeks. Attached sample report.

I have created calculated field 'Sort' and set top 3 in Country filter by Sort field.

Sort = if [Week]='2013 W34' then [Sales] else null end

I have hard coded W34 in Sort field but it has to remove and to set condition to get max week by default.

How can get top 3 sales country based on max week by default?

Best Regards,

Rajini

• ###### 1. Re: How to get top 3 sales country based on max week in line trend

I believe this is it - 2 table calculations, a simple index that ranks the countries by whatever we choose, and a lookup that finds that index in the last week of the data. The secret source is in the way we set up the partitioning of two calcs. The index ranks the countries by descending sum(sales) and resets each week. The lookup provides the index for the last week of the data.

We can then filter on that lookup.

• ###### 2. Re: How to get top 3 sales country based on max week in line trend

Shouldn't the top 3 countries be CN, FR, UAE though?  I'm seeing that when only the last week is used, the Index ranks these as the top 3, but when all weeks are showing, the rank for each week is incorrect (and matches Alex's results).  I'm not sure what is causing this.

• ###### 3. Re: How to get top 3 sales country based on max week in line trend

Dammit, let me look

• ###### 4. Re: How to get top 3 sales country based on max week in line trend

Its weird, because this is what I see when looking at the last week versus all weeks--the Index returns different results:

• ###### 5. Re: Re: How to get top 3 sales country based on max week in line trend

Yep, one of those odd occasions where you have to use the other options to set the partitioning. Good catch:

• ###### 6. Re: Re: How to get top 3 sales country based on max week in line trend

I would guess it's something to do with the sorting that gets fixed with my second method, but I cannot articulate why the index is wrong. Maybe Jonathan Drummey?

• ###### 7. Re: Re: How to get top 3 sales country based on max week in line trend

Nice, I had done everything except setting the Sort order to Sum of Sales, Descending.

I don't understand the Sort order defined in the "Ranking in last week" filter--it seems that setting that to "Automatic" yields the same results--is there a reason for this sort?  I'm just trying to pick up what I can by trolling this forum, appreciate the insight!

• ###### 8. Re: Re: How to get top 3 sales country based on max week in line trend

Automatic should be fine as automatic would imply an alphabetical sort I think. I've always liked to explicitly set it though - as it's a string, min, asc means alphabetical from A to Z

• ###### 9. Re: Re: How to get top 3 sales country based on max week in line trend

OK, thank you for clarifying!

• ###### 10. Re: Re: How to get top 3 sales country based on max week in line trend

Matthew - next time around please post a packaged workbook, when it comes to table calcs a screenshot is hardly ever enough.

I'm almost 100% positive that the addressing and partitioning is not the same as what Alex had set up, I'm guessing that it's still using the Automatic sort and not the custom Field sort in the advanced dialog. When I do the addressing on Week, Country (in that order) sorted by SUM(Sales)/Descending, restarting every Week, I see the same results as the Top 3... worksheet.

Also, one thing to be careful of when trying to match up results is to pay attention to the sorts on any pills in the view. For example, in the screenshots you have a Country sorted by something, I'm guessing SUM(Sales)/Descending. That can trip us up in a couple of ways. One is that the pill sort is done across the entire data set, another is that the default addressing for table calcs will use those pill sorts.

When using the Automatic sort, the dimension(s) used for addressing are sorted by whatever the pill sorts are. When using a custom Field sort, the "set" of dimension(s) used for addressing are sorted by the given measure. So in Alex's solution, with both Week and Country in the addressing and the custom sort, all of the combinations of Week and Country are then sorted. With the restarting each Week, Tableau then computes a new range of Index values for the countries within the week. Does that make sense?

Jonathan

• ###### 11. Re: How to get top 3 sales country based on max week in line trend

Yes, it makes sense to me at the moment.  I was originally missing the custom field sort.

I've been trying to replicate the whole process, and if I remove the "Ranking in last week" filter, and try to put it back on the filter shelf, it gives me one checkbox with the number "3" in it, rather than allowing me to choose the top 3 rankings -- I cannot figure out why this is happening, either.

• ###### 12. Re: How to get top 3 sales country based on max week in line trend

That's because initially it will default to table across - the last value for index on weeks is 3.

• ###### 13. Re: How to get top 3 sales country based on max week in line trend

OK, I figured it had something to do with that, but when I change the Compute Using settings, I still cannot choose 1-3.

• ###### 14. Re: How to get top 3 sales country based on max week in line trend

If someone can explain how I can re-create this scenario, I have tried just about everything I can, but I always get the checkbox with "3" next to it, rather than the ability to choose the top 3.

1 2 Previous Next