7 Replies Latest reply on Mar 15, 2012 6:38 AM by Jonathan Drummey

    Trying to create a viz showing product penetration growth (decline)

    bruce lomasky

      My data file has the following fields:   date, salesrep, customer and product.  The problem I am trying to solve is showing the product penetration growth (or decline) for a selected date range compared to a previous one.  There are 12 different products, which means the product penetration delta can range from -12 (if we sold them every product in the previous period and none in the current) to +12 (the reverse!).  What I want to see, for each sales rep is the number of customers in each of the 24 possible categories.

       

      I have started with 2 calculated fields, (current and previous) with an expression of :  COUNTD(IF (date > Param1stDate and date < ParamLastDate then product else NULL end)

       

      I am stuck trying to figure out how to graph the results I want now.

       

      Bruce Lomasky

        • 1. Re: Trying to create a viz showing product penetration growth (decline)
          Jonathan Drummey

          Hi Bruce,


          In terms of how to graph this, to start it looks like a crosstab that you could then get fancy with by turning into a heatmap. I'd suggest that you set up the Product Penetration Delta as a dimension in the view and put that on the Columns shelf, with Sales Rep on the Rows shelf. Then your Customer Count field would go on the Text shelf, so you'd end up showing one cell per Product Penetration Delta/Sales Rep combination. Once you get that working, then you could change it into a heatmap where squares are sized and/or colored based on the Customer Count.

           

          There are some other options you might try, but the # of Deltas is limiting because 24 different categories per sales rep gets to be too many for people to be able to make useful distinctions when using color, lines, etc. You might try creating some custom groups of the Deltas to shrink that down, and then work with other types of views.

           

          Jonathan

          • 2. Re: Trying to create a viz showing product penetration growth (decline)
            bruce lomasky

            Thanks for the help Jon, but I have an issue.  I am only seeing ONE count per rep.  I am attaching an image of my vis.  Not sure what I am doing wrong.

             

            BruceSheet 8.jpg

            • 3. Re: Trying to create a viz showing product penetration growth (decline)
              Jonathan Drummey

              Hi Bruce, I'm not sure what your data looks like, so I can't be sure of what the calculations are doing. Can you post a a packaged workbook with some sample data?

               

              Jonathan

              • 4. Re: Trying to create a viz showing product penetration growth (decline)
                bruce lomasky

                Thanks, I am attaching my viz.  The detail tab is correct but when when try to get the salesrep totals, I get only one item per salesrep.

                 

                Thanks

                 

                Bruce

                • 5. Re: Trying to create a viz showing product penetration growth (decline)
                  Jonathan Drummey

                  Hi Bruce,

                   

                  On my first pass I could see that the Delta Product Mix calculation is definitely only returning one value per sales rep. Besides the Product Penetration view, you can see this if you drag the Delta Product Mix measure onto the Rows shelf in the Product Mix Detail view - there should be multiple rows per sales rep. This is because you've taken customer off of the level of detail, so the computation is happening over all customers and not for each customer.

                   

                  So, here's what you need to do in the Product Penetration view:

                  1. Drag custNo onto the Level of Detail shelf. You will now see the spread out Product Mix, with a lot of fuzzy looking 1's because the noCusts calculation that is COUNTD([custNo]) is now working at the level of detail of custNo so it only returns 1 for each customer.

                  2. Create a table calculation - I called it "Customer Count" using the formula:

                  IF FIRST()==0 THEN

                  WINDOW_COUNT(COUNTD([custNo]))

                  END

                  3. Drag that field onto the Text shelf. You're still going to see lots of 1s.

                  4. On the Rows shelf, click on the blue AGG(Delta Product Mix) pill and uncheck "Ignore in Table Calculations". This will let the Delta Product Mix field be included as a dimension in the table calculation. (Normally Tableau ignores calculated dimensions).

                  5. Click on the green Customer Count pill on the Text shelf and set it to Compute using custNo. You will now see a correct batch of numbers.

                  1 of 1 people found this helpful
                  • 6. Re: Trying to create a viz showing product penetration growth (decline)
                    bruce lomasky

                    WOW!

                    1st, I want to say wow for taking the time to understand my problem

                    2nd, I want to say wow for explaining the solution in great depth, It has helped my understanding of the product and those silly calculations.

                     

                    Question:  When I right click and select view data, I only see 1 customer no matter what the count is.  Can I change this?

                     

                    Again, thanks a LOT!  You Rock!

                     

                    Bruce Lomasky

                    • 7. Re: Trying to create a viz showing product penetration growth (decline)
                      Jonathan Drummey

                      You're welcome!

                       

                      The Customer Count is a calculated field that is shown at the intersection of Delta Product Mix and sales rep, and regardless of whether there is overlapping text you're only seeing the "topmost" row of data. The IF FIRST()==0 ... END bit in the Customer Count calculated field is there to prevent Tableau from showing overlapping text in the view, even if you comment that part out then View Data only shows one customer.

                       

                      I tried creating a dashboard with the Product Penetration view and a separate view that would have an Action Filter to show customers for the given intersection of rep and Delta Product Mix, but it seems like Tableau won't consider Delta Product Mix to be an allowed dimension for the target in the Action Filter, (probably because it's a calculated dimension based on a table calc, you can create Sets on other calculated fields). I also tried to create a Set on the sales rep and Delta Product Mix, or even just Delta Product Mix, but Tableau won't allow that either, I'm guessing for the same reason.

                       

                      You could create a different crosstab to show customers by just duplicating the view and dragging custNo onto the Rows or Columns shelf, and do some separate filtering and sorting by Delta Product Mix and sales rep, but that's about all I can think of.

                       

                      Jonathan