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

    Frank Casella

      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