2 Replies Latest reply on May 31, 2016 1:39 PM by Frank Casella

# Count the number of times a state appears in the top n of multiple metrics

Hello,

I've created a simple viz that shows the worst three states for two products across multiple metrics.  it looks something like this:

MetricProduct 1, Rank 1Product 1, Rank 2Product 1, Rank 3Product 2, Rank 1Product 2, Rank 2Product 2, Rank 3
Metric 1NevadaMichiganConnecticutArkansasWest VirginiaVermont
Metric 2TexasMichiganMississippiKentuckyTexasArkansas
Metric 3NevadaConnecticutKentuckyArkansasVermontTennessee
Metric 4MichiganNevadaFloridaFloridaDelawareNevada
Metric 5NevadaFloridaCaliforniaFloridaDelawareNew Hampshire

The column fields are Product and Sum(Metric) with a table calculation to rank by state within the product and metric partition.  The row field is simply the metric name.  The source data is a straight table with four columns: State, Product, Metric Name, Metric.

I'm trying to calculate the number of times a state appears in the top 3 for each product. In this example, I'd expect Nevada to be a 4 for product 1 and a 1 for product 2. Right now, I'm using the rank table calc off of the metric sum, but I've also created a State_Rank_Index calculated measure using the index() function that does the same thing.  This is also a table calculation with results computed along state for each product, metric name, sorted descending by sum or metric.

I've tried a couple different approaches to count the number of times the index is less than 4 for each state and for each product, but am having no luck...any suggestions would be greatly appreciated.

Thanks for the help,

-Frank

• ###### 1. Re: Count the number of times a state appears in the top n of multiple metrics

Hey Frank,

Would you be able to provide a packaged workbook so we can take a look at your issue? If you have sensitive data, please check out Anonymize your Tableau Package Data for Sharing.

Thanks,

-Diego

• ###### 2. Re: Count the number of times a state appears in the top n of multiple metrics

Hi Diego,

I've created a copy stripped of all identifying data, but can't seem to figure out how to upload it.  I read the article advising to use the Tableau Public server.  When I try to go this route, I keep getting errors telling me the server cannot be reached. It looks like my company has placed an access restriction that prevents me from going to this.

Any suggestions and thanks for your help?

Frank