1 Reply Latest reply on Aug 14, 2012 5:36 AM by Russell Christopher

    Stores not carrying...

    Sam Bruce

      In the attached workbook I have a set of data that represents sales by store for 6 different brands.  Not all stores carry all brands.  In that case there is no record for them.  I can use the distinct function to see which stores have the product but what I really want is a list of stores, ideally a filtered by city, to highlight the stores that do not have the product.

       

      Ultimately what I am after is a map that has a filter for city (or region,territory, etc) and a filter/color for stores carrying/not carrying.  Within the table a percent of stores carrying would also be wonderful!

       

      Any help is appreciated,

      Sam

        • 1. Re: Stores not carrying...
          Russell Christopher

          Here's a start for you. The approach I've taken is to create a calculated field on a per-brand basis that figures out if said brand has sales or not. I only created fields for brand A and B ("Carrying Brand A", "Carrying Brand B")

           

          Those "per-brand" calculations can then be knitted together in a different field ("Color Chooser") to display different values (which drive color) based on the output of the "Carrying Brand ?" fields.

           

          I also used a parameter to allow the user to choose WHICH brand he/she is interested in seeing. It's called "Choose a Brand". For fun, I included a field "Sales Sizer" which is used to drive circle size - If a brand has no sales it returns the number "1", else it returns the actual sales for that brand.