9 Replies Latest reply on Mar 7, 2018 7:55 AM by Sarah Battersby

    How to create a cumulative line chart for map distance

    Robert Abramov

      I've created a dynamic mapping of certain storage locations as per Sarah Nells post:Distance Mapping with Radius Filter. Now I'm trying to do 2 separate things:

      1. Create a cumulative line chart that shows how many storage square feet are available in a 20 mile radius by distance away from the selected storage location.

           Ex: The selected storage facility has 20,000 sq ft. A storage unit 2 miles away has an additional 40,000 sq ft (60,000 cumulative sq footage at 2 miles away on the x-axis). Another storage unit 4      miles away has 10,000 sq ft. (70,000 cumulative sq footage at 4 miles away on the x-axis), etc. However, I'm only able to get a scatter-plot as is exhibited below:

      2. I am trying to get a "network" of the total square footage available within a mile radius...for every store that I've put on the graph. Then I would like to be able to highlight a certain area of the map of the US, and have a separate part of the dashboard list the top 10 addresses with the most square footage within a 20 mile radius.

       

      If anyone has had any experience with either of the above, I would sincerely appreciate a few tips and tricks!

        • 1. Re: How to create a cumulative line chart for map distance
          Patrick A Van Der Hyde

          Hello Robert,

           

          Do you have a .twbx to share?  I am thinking a LOD calculation or a window_sum() calculation of the filtered results will show the desired total when selected for any point on the map but I'd have to see a sample workbook to test it out for you. 

           

          The second option appears much harder -- a top 10 of a dynamic calculation based on the radius on the map.  I think we need to calculate all of the totals square footage for each point without using the map. I do not know of a way to accomplish this but I'll bring it to the attention of our maps team.

           

          Sarah Battersby

           

          Patrick

          • 2. Re: How to create a cumulative line chart for map distance
            Robert Abramov

            Sure - here is an example using Starbucks stores in NYC. For this example, instead of using square footage, can we just used the count of the # of stores instead? I can't share my original workbook, as it has some proprietary data in it (square footage).

            • 3. Re: How to create a cumulative line chart for map distance
              Sarah Battersby

              Hi Robert,

               

              I'm not sure if I'm hitting the right target here with my example, but I worked up a dashboard with a map showing Starbucks locations in NYC.  I'm calculating distance using the lat and lon in the parameter because I find it more flexible than using a table calculation, but have set up the dashboard so that when you select a different starbucks location it'll update the map (within parameter distance or not), and update a list of the top 10 stores in the area (I'm using facility ID as my measure, but this could be square footage or whatever you want).

               

              Let me know if this is getting close to what you are trying to do and if you have any questions about how I put it together. The workbook (in Tableau 10.5) is attached.  I can save as an older version if you need.

               

              -Sarah.

               

              2 of 2 people found this helpful
              • 4. Re: How to create a cumulative line chart for map distance
                Boston Woodworth

                Hi Sarah,

                 

                Thanks for the insight - this is incredibly valuable. Robert and I are working on this project together, so I'm responding on his behalf while he is traveling abroad

                 

                What you've provided above is incredibly helpful - I'd be interested to hear why you consider calculating distance without table calculations to be more flexible! However, we have a few questions after taking another spin at the Viz:

                 

                1. How can we develop a line chart that shows the running sum of employees based on our distance parameter?

                    (Sum of Employees By Distance Sheet)

                    - In the attached workbook we have Running Sum of Employee Headcount as the Y axis & Distance as the X. However, we are unable to create a running total calculation in the graph. We want the user to see the number of employees at each location in addition to the running sum of total employee headcount given the distance parameter.

                 

                2. How can we create a grand total column at the bottom of the Sum of Employees by Location chart if we are using a discrete variable?

                    -  We want to highlight employee headcount per location (based on the distance parameters) in addition to the total employee headcount within the distance parameter.

                 

                3. How can we create a stacked horizontal column chart to show the number of store employees at each location against the total employee headcount for the selected distance parameter? (Sum of Employees by Location Sheet)

                 

                Sincerely appreciate your input on this!Sarah Battersby

                • 5. Re: How to create a cumulative line chart for map distance
                  Sarah Battersby

                  Hi Boston,

                   

                  Thanks for the additional questions - they're on my radar and I'll start thinking about them (have a few other fires I need to take care of first though, so it might be early next week that I can really get back into this).

                   

                  As for why I find the non-table calc distance calculating option more flexible... it's mostly that I find the table calcs to be more fiddly than needed for this type of application.  I'm not expert enough to feel confident I have the right level of detail for the calculation, especially when trying to use them in other calculated fields (e.g., to determine whether the distance is in a set threshold or not).  So, it may really be *more* flexible to use the table calc, but for me it's just too much of a hindrance for this type of calculation / use. 

                   

                  One of these years I'll actually sit in on some of the TC sessions on mastering table calcs so that I can up my Tableau wizardry game

                   

                  More on your other questions later when I have a chance to dig in...

                   

                  -Sarah.

                  1 of 1 people found this helpful
                  • 6. Re: How to create a cumulative line chart for map distance
                    Patrick A Van Der Hyde

                    I went ahead and marked Sarah's first reply as correct so other customers with a similar question can easily locate the example provided.

                     

                    Patrick

                    • 7. Re: How to create a cumulative line chart for map distance
                      Sarah Battersby

                      Hey Boston - Here are some more thoughts on what I have interpreted from your questions...

                       

                      1. How can we develop a line chart that shows the running sum of employees based on our distance parameter?

                          (Sum of Employees By Distance Sheet)

                       

                      Workbook attached - see 'Dashboard 2'

                       

                      I was able to get the running sum of top 10 closest locations by using the non-table calc version of the distance calculation (maybe it's possible with the table calc, but I do tend to default to non-table calcs because I find them easier to work with for this type of thing...too many moving parts on getting the table calcs tuned just right for me...).

                       

                      For the cumulative sum I'm using Distance on columns, Sum of employee headcount on rows - and using a quick table calc here for 'Running Total'

                      I added a filter to just show the top 10 stores (but you could drop this off and see the cumulative total for all stores, or all stores within your search distance)

                      Because I'm using top 10, I added the 'within distance' dimension to context so that the top 10 was calculated correctly from the filtered data.

                       

                      2. How can we create a grand total column at the bottom of the Sum of Employees by Location chart if we are using a discrete variable?

                       

                      A few things you could do here... one would be to add a column grand total (Analysis -> Totals -> Show Column Grand Total).  Personally, I don't like the way that looks on the bar chart, so if it were my viz, I'd probably create a separate sheet that just listed the total for all stores and add that to the dashboard as a floating element.  You could drop it wherever you want at that point.

                      Another option would be to try a level of detail calculation that summed the employees within the distance and just add that sum to the field header.  I went ahead and added that into the 'Individual Store (2)' worksheet so that you can see how that works.

                       

                       

                      3. How can we create a stacked horizontal column chart to show the number of store employees at each location against the total employee headcount for the selected distance parameter? (Sum of Employees by Location Sheet)

                       

                      Is this the sort of thing that you want?

                       

                       

                      Let me know if these work out for you or if they bring up more questions.

                       

                      -Sarah.

                      1 of 1 people found this helpful
                      • 8. Re: How to create a cumulative line chart for map distance
                        Boston Woodworth

                        Hi Sarah Battersby,

                         

                        This is incredibly helpful! Thank you for breaking down the individual components and explaining your preference for avoiding a table calc in the distance equation.

                         

                        Great work!!

                        • 9. Re: How to create a cumulative line chart for map distance
                          Sarah Battersby

                          I'm glad this helped out; it was a fun problem to work on!

                           

                          -Sarah.