3 Replies Latest reply on Oct 1, 2013 6:21 AM by Jonathan Drummey

    How to do a running sum using Lat, Long ?

    Siva Ganapathi

      I have 3 columns in my data.

       

      Lat

      Long

      Ship Volume.

       

      My objective here is to show the Lat, Longs on the map that ship X% (like 20%, 40%, 60% etc.) of the total volume. I did a running sum and then did a % on the running sum using total shipments to get the cumulative % of the shipments. But this only works if the ship volume is sorted in the descending order and I am not able to sort Ship Volume when Lat and Long are placed as continuous dimensions in the rows and columns field to view them in the map. Any ideas here ?

        • 1. Re: How to do a running sum using Lat, Long ?
          Jonathan Drummey

          You need to create a dimension and then sort that on the volume, one way is to create a combined string of the Lat & Long. I did so in the attached.

           

          Jonathan

          • 2. Re: How to do a running sum using Lat, Long ?
            Siva Ganapathi

            Jon,

             

            Thanks a ton for helping me out on that. It did work. I have another question though. In the same example, I want to introduce another dimension, lets say shipment mode (Road or Rail) and color the lat long using the shipment mode dimension. I can drag the ship mode dimension to color shelf to achieve this but the issue I am having is that the cumulative % calculation then gets partitioned by ship mode. Is there a way to have the calculation not include a particular dimension?

            • 3. Re: Re: How to do a running sum using Lat, Long ?
              Jonathan Drummey

              There are a couple of different ways around this depending on your data (that's why we often ask for a packaged workbook - So Your Question Didn't Get Answered...). If each point is unique such as what I set up in the attached using the Lat&Long dimension, then you can use ATTR(Shipment Mode) to turn Shipment Mode into a measure that won't affect your table calculations.

               

              An alternative if Shipment Mode has to be a dimension is to use an Advanced Compute Using where the Lat & Long and Shipment Mode are both in the Addressing, with At the Level set to Lat & Long. This causes Tableau to only imcrement for new Lat & Longs, effectively ignoring Ship Mode in this case.

               

              I set up both of these in the attached. In general, I'd use the former because it's a lot easier to understand and maintain, and more performant. In general, keeping the number of dimensions to a minimum makes the queries run faster, plus makes table calculations easier to use.

               

              Jonathan