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

# How to do a running sum using Lat, Long ?

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 ?

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 ?

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 ?

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