Want to start off by saying, brilliant work on the Distance Calcs! This looks beautiful!
What you are after is made difficult because you are working with Blends, and your secondary datasource doesn't have "Product" in it...
I have an idea how we can get around this, but Blends are not my strongest suite.
I'm going to ping Jonathan Drummey on this. He is a true Zen, and if anyone can get this to work/give a detail explanation of how....he's the guy!
A few notes:
1) The map is plotted out using Tableau's auto-generated lat and longs
while the calculations are dependent on the lat & long in the data. As much
as Tableau tries to make their lat & long source accurate they aren't
necessarily going to be the same as the ones in the data so the distance
calculations could be different from the plotted locations when you are
using Tableau's auto-generated lat and longs. My suggestion would be to use
your lat & long.
2) If you want to use a Filter Action to go from sheet to sheet (where one
of the target sheets is the map) then the primary source for the map has to
have Product in it. Rody had already mentioned this.
3) Since the ZipCode Database source has more zip codes than the Superstore
source, if you want to display all the zip codes and get the Filter Action
from #2 then you'll need to create a primary source that has all the zip
codes and all the detail rows. Tableau v9.3 supports a UNION that could be
used between Excel worksheets, it's also possible to do this with left
joins in the same source. v10 will have some improvements to this. If you
don't need to display all the zip codes in the data then you could just
"flip the blend" for the map and make Superstore the primary and the zip
code source (for the lats & longs) the secondary.
4) Once you have the sources all set up then you can build out your calcs &
5) The view is built using a parameter for picking the center zip code, if
you want that to be dynamic then you'd need to use something like a
secondary source for the zip, see
for details. The third example has a use case somewhat similar to yours.
On Wed, May 4, 2016 at 12:57 PM, Rody Zakovich <email@example.com
Appreciate the replies, gents.
Jonathan Drummey - Your explanation makes sense, but getting the rubber to meet the road is proving to be another story.
Because I only need to reflect the ZIP codes which are present in the Superstore data, I "flipped the blend" as you mentioned in item #3. When I do that, however, I'm unable to get my Central Latitude/Longitude calculations to behave. I'm either getting NULL values in the data set or invalid formula errors due to the need to pull Lat/Long data from the blended data source. I've attached a new version of the workbook showing this and included the short version of my testing and results below.
Any idea what needs to change in order to be able to calculate a central Lat/Long based on the Target ZIP parameter while leveraging the SuperStore data source as primary without adding Lat/Long data to the SS data source itself?
Worksheet: Map CT (Working)
Field: Central Latitude (ZCD)
Compute using: Zipcode (Zipcode Database)
ATTR(IF STR([Zipcode]) = [Target ZIP] THEN [Lat] END)
Worksheet: Map CT (Non-working)
Field: Central Latitude (SS)
Compute using: Advanced (Zipcode, Postal Code)
Formula Attempt #1:
ATTR(IF STR([ZIPCode Database].[Zipcode]) = [Target ZIP] THEN [ZIPCode Database].[Lat] END)
Result: Valid formula, NULL values
Formula Attempt #2:
ATTR(IF STR([Postal Code]) = [Target ZIP] THEN [ZIPCode Database].[Lat] END)
Result: Invalid formula, "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources
Formula Attempt #3:
(IF ATTR(STR([Postal Code])) = [Target ZIP] THEN MAX([ZIPCode Database].[Lat]) END)
Result: Valid formula, NULL values