1 Reply Latest reply on Oct 3, 2012 10:24 AM by Jonathan Drummey

    How to calculate product count against total stores?

      Hello,

       

      I am working on calculating the weekly product count against the weekly total number of stores in my region and I wondering if anyone could help me.

                                                                                                                                                                                                                     

      Example:

      Region

      Product

      week

      Product Count

      Total Stores*

      Percentage

      East

      Jellybeans

      1

      24

      110

      22%

      2

      10

      111

      9%

      3

      19

      111

      17%

       

      My goal is to be able to determine a percentage based on the (weekly product count of product) divided by the (weekly total stores per region )  .

      *I determined the Total Stores number by using the Unique Store Id and changing the dimension (discrete) to Count Distinct to give me the total number of stores for each region (continuous).

      Every time we filter between the product level, the Total stores is the same as the product count.

       

      Thank you,

      Alan 

        • 1. Re: How to calculate product count against total stores?
          Jonathan Drummey

          Hi Alan,

           

          The "regular" filters on fields are used within the query that Tableau issues to the data source, so when you filter on products you are losing all Store IDs that don't have the filtered products.

           

          The approach around this is to use a filter based on a table calculation. Table calc filters are applied after the query is executed and most all computations have occured, so they effectively hide data. The solution in this case is to use the LOOKUP() function, such as LOOKUP(ATTR([Product]),0). Put that field on the Filter Shelf in place of the existing Product filter and you should be all set.

           

          Jonathan