13 Replies Latest reply on Sep 18, 2012 2:21 PM by Shawn Wallwork

    Sales Over Time & Territory

    Shawn Wallwork

      My Tableau heroes this week are @Jeff Rice and Tracy Fitzgerald. Jeff for taking the time to demo a way to animate the hurricane viz, a technique I was able to adapt for this viz. And Tracy for sponsoring the tdt and getting Jeff to come talk to us. Thanks Jeff & Tracy!


      The assignment for this viz was to visualize sales over time and territory. The data shows sale counts for three years for a door-to-door sales crew. The client wanted to know if the crew 'exhausted' a neighborhood and then had to move on, constantly having to expand into new territory to keep sales up. Or was the crew able to re-visit fertile ZIP Codes again and again. So this is the viz I came up with:


      Sales T&T.PNG


      I used the Page Shelf to animate the sales over time for both the map and the bar charts. The lines on the map are where Jeff gave me a hand. By changing them from line marks to circle marks (counter-intuitive, as Jeff mentioned) I was able to use the history trails to draw the lines over time. The lines themselves are the AVG(Latitude) & AVG(Longitude) for all ZIP Codes with at least one sale for that month. This gives a decent representation of the center of the sales effort, and more importantly shows any big moves into new territory (the long yellow-orange line moving south for instance). This average trick was another one I picked up on the forums, unfortunately I couldn't find the original post, so I can't give a shout-out/thanks for that one.


      Improvements: The one thing I'm not completely happy with is that the labels on the bar charts don't remain visible throughout, even though the bars do. If anyone could figure out how to over-come this without messing up the way the map displays, I sure would be grateful. Also I wasn't able to work out the averages to be weighted averages based on number of sales per zip.


      Other than that, I'm happy with the way it turned out, and more importantly so is my client.


      Comments appreciated.


      EDIT: I've now attached the latest version of this viz (Sales Territory Over Time 9-16-10a), which includes Richard's improvements. At first I was concerned that Richard's solution wouldn't scale well, but as long as I keep the dashboard size within a narrow range, it works just fine.


      EDIT #2: Sales Territory Over Time Ver2 is a trial of the idea of making the marks persistent. Unfortunately it covers up the lines too much.


      EDIT #3: Sales Territory Over Time Ver3 implements a solution to Richard's concerns over the misleading nature of using a running total with marks that are not persistent. (More comments below.)




        • 1. Re: Sales Over Time & Territory
          Richard Leeke

          Nice work as usual, Shawn.


          I've modified one of the two bar charts so that the labels remain visible - I thought I'd leave you to work out how I did it and do it to the other one - following the "give a man a fish" principle.


          The average latitude and longitude are already implicitly weighted by number of sales per zip. The reason for that is that you don't have zip on the shelves for the history line axis in your dual axis map - so the positions are averaged over the all rows - so if you had 2 sales in zip 1 and only 1 sale in zip 2, the average location would be closer to zip 1.


          One other thing that I thought would possibly be nicer, but I couldn't see how to do without far too much convoluted data wrangling, was to keep the running sum of sales marks shown even for months when there were no sales in that zip. At the moment, when they disappear and then come back again it sort-of gives the impression that the newly reappeared mark represents the sales for that month rather than cumulatively.


          I think you could achieve it by including zero sales rows in your data for all zips after they have had any sales. I haven't thought too hard about it, but this looks like a case where it would be good to have control over "padding the domain". I think Tableau would automatically create those empty rows for you in some configurations of your shelves, but not this one. Joe (and probably Jonathan) doubtless know the rules on that.

          • 2. Re: Sales Over Time & Territory
            Shawn Wallwork

            Hi Richard. Thanks for the help. I've got my cappuccino, sitting down ready to learn to fish. Good to know about the weighted avg lat/longs. Sometimes I just don't have a clear mental picture of what's getting sliced & diced how/when. I've got more work to do in that area. Maybe I'll switch my tcc12 schedule around and attend Ross Bunker's session.


            Now for some fishing...



            • 3. Re: Sales Over Time & Territory
              Richard Leeke

              It often helps to filter down to just a tiny sample of the data when you're trying to get your head around things like the level that that average was working at. Here I've filtered to just two zips: 94014 with 1 sale and 94533 with 5 sales for May 2009 and changed the mark type for the average position to a cross.


              May Sales for 2 Zips.png


              It's pretty clear that the average position is weighted how you wanted it.

              1 of 1 people found this helpful
              • 4. Re: Sales Over Time & Territory
                Shawn Wallwork

                Yeah, that is a good way to figure it out. But how do you learn what/how Tableau is 'thinking'. For instance, what if I wanted the average figured the other way, i.e. absolute without being weighted by sales count? And more importantly, how to know/learn how it's working without all the trial and error? OK, so a lot of rhetorical questions, but....


                BTW, your original labeling solution was clever. When I tried the same dual axis solution, I couldn't work out the label position problem. Thanks for that.



                • 5. Re: Sales Over Time & Territory
                  Richard Leeke

                  All I can offer on the rhetorical questions it's that you have to think how Tableau will be thinking. So on the average position question, I just looked at what you had and saw that you had got average lat and long by month of sale (they were the only dimensions in play). So it had to be the average of all sales. if you had wanted the average of all of the distinct positions you would have needed to return one row per zip (as you have on the other axis in the dual axis map) and then use a window average.

                  1 of 1 people found this helpful
                  • 6. Re: Sales Over Time & Territory
                    Shawn Wallwork

                    '...have to think how Tableau will be thinking.' Yep there's the rub! Ahhh, someday. But then 8.0 will come out, right. Thanks for the help Richard.



                    • 7. Re: Sales Over Time & Territory
                      Richard Leeke

                      BTW - I noticed that my phone had said "the other acid in the disk acid map" when I was trying to say "dual axis", in that last post. I guess you figured out what I was trying to say...

                      • 8. Re: Sales Over Time & Territory
                        Jonathan Drummey

                        Hi Richard,


                        I played around with the domain padding for awhile and got *this close* to a solution, only it won't work. Tableau will pad the domain for dimensions on the Rows and Columns shelves, but not LoD as far as I can tell. Maybe Joe Mako has an idea, he only solution I know of to get the marks to stick around would be to use Custom SQL to build your own padding.


                        Here's an example using the attached workbook:


                        1. For date fields on Rows or Columns when there is no table calc in the view, Tableau doesn't pad the domain - see "raw data - no padding" in the attached.


                        2. Add a table calc to the view, and bam, there's padding - that's "see padding" in the attached. Note that table calcs return values when there is data (like for zip code 93210 in April 2012, but there are no values for Latitude and Longitude).


                        3. I thought I could take advantage of this and pad out Latitude and Longitude values, and pad out a date value for use on the Pages shelf as well. These are the TC Month of Sale, TC Avg Latitude, and TC Avg Longitude calcs. You can see them in the "calcs to use padding" worksheet and they provide values.


                        4. You can even use the Pages Shelf and it'll work just fine, returning a value every time, see "pages shelf works".


                        5. In the desired view, the Date of Sale fields are not on Rows or Columns, but on the Level of Detail Shelf. However, as soon as those go on the LoD then padding goes away. You can see that in the "lose padding when date on LoD" workheet, like in the original view the marks come and go.



                        1 of 1 people found this helpful
                        • 9. Re: Sales Over Time & Territory
                          Richard Leeke

                          Yeah, I don't know of any way to get this to work with padding with the desired shelf configuration, either.


                          I really think there's a good case for exposing control of when padding happens to the user - but I suspect that this is like a few cases where Tableau judge that the downside of the extra confusion of adding yet more visible knobs and levers outweighs the existing downside of it already being confusing and sometimes not doing what you want.

                          • 10. Re: Sales Over Time & Territory
                            Joe Mako

                            Currently in Tableau, padding is not something you can directly control, but you can indirectly control it with the following:


                            - Arrangement of dimension pills on shelves, Rows, Columns, and shelf on the Marks card, eg making a crosstab

                            - Using a date pill for the compute using

                            - Show Missing Values for discrete date pills

                            - Your Mark type, Line and Polygon mark types will pad more with a dimension on the Level of Detail shelf, depending on your data contents.


                            Each combination here has different results and effects padding differently. Another factor is how is padded marks can be detected and handled either in filtering or in formulas, because the concept of "Missing" is not equal to Null. The order of operations needs to be considered.


                            I have read through this thread, but I am not sure exactly what you are looking to accomplish. You are welcome to contact me.

                            1 of 1 people found this helpful
                            • 11. Re: Sales Over Time & Territory
                              Shawn Wallwork

                              Hi Joe, thanks for the offer of help, but with Richard's help I was able to accomplish everything I wanted to (labels on the bars, and weighted averages for the lats/longs).


                              Richard made this suggested improvement: "One other thing that I thought would possibly be nicer, but I couldn't see how to do without far too much convoluted data wrangling, was to keep the running sum of sales marks shown even for months when there were no sales in that zip." Which obviously peaked Jonathan's curiosity, so he did all that padding work (interesting stuff Jonathan).


                              As far as I'm concerned the viz is pretty well finished, but I went ahead and posted another version of it, which keeps all the marks hanging around, as Richard suggested. (Posted to original post). Not being adept at convoluted data wrangling, I went for the easier solution: I switched the page shelf history from 'Trails' to 'Both' and made the marks 100% transparent (which is actually 100% opaque, but that's a different post). Anyway, I'm not sure this works as well because it obscures too much of the lines, which is the real story. Having said that, I'll probably include it as an alternative for the client, but I'll get rid of the averages so I don't get peek-a-boo lines. It has the interesting effect of revealing ZIP Codes that haven't been worked in a while. Might work even better if I colored by quarter, instead of year.


                              Thanks for your comments about padding, I'll keep working to figure out these sorts of things -- trying to reduce the frictions.



                              • 12. Re: Sales Over Time & Territory
                                Richard Leeke

                                Much simpler way of achieving what I was talking about - and I agree that it turns out not to be very effective because you end up with such a dense mass of marks.


                                However, it did highlight to me that the original version with the marks showing the cumulative sales over time in each zip, but only shown when there was a sale in that zip is a bit misleading. The existence of a mark for a zip means that there was at least one sale in that zip for that month, but the size shows total sales in that zip since the year dot. So the impression is a bit misleading, I think. It suggests that the team is becoming more and more effective.


                                So I think just showing the month's sales, rather than the running sum of sales on Size might be better.


                                I vaguely wondered about some way of using the colour shelf to show how many visits there had been to each zip - but I think that is just trying to load too much meaning into one viz (and anyway, it made something very strange happen with the history trails).

                                • 13. Re: Sales Over Time & Territory
                                  Shawn Wallwork

                                  Richard I think you're right about the misleading nature of the running sum with disappearing marks. So I've posted Ver3, which adds a toggle for the size shelf. I think the real usefulness for this is when you stop the animation and then toggle back and forth. Thanks for the suggestion.