the problem you are encountering is due to the blending of the two datasources. During the blending, Tableau is performing a left join, but where there is more than one result from the secondary data source that matches, it returns "*".
With the regions, there is more than one month per region, but the secondary data source does not know which month to return, so gives a "*"
The solution is to use the data with the months as the primary datasource, and the polygon file as the secondary. Unfortunately, doing this within Tableau again hits the "*" issue.
One solution is to make the join as part of the data connection. I've attached an altered workbook in which I've done this, and it now works correctly across the months.
The Custom SQL for this is:
SELECT [Sheet3$].[Month] AS [Month],
[Sheet3$].[Region] AS [Region],
[Sheet3$].[Sales] AS [Sales],
b.[Latitude] AS [Latitude],
b.[Longitude] AS [Longitude],
b.[Order] AS [Order],
b.[PolyID] AS [PolyID],
b.[State] AS [State]
(SELECT [Sheet2$].[Latitude] AS [Latitude],
[Sheet2$].[Longitude] AS [Longitude],
[Sheet2$].[Number of Records] AS [Number of Records],
[Sheet2$].[Order] AS [Order],
[Sheet2$].[PolyID] AS [PolyID],
[Sheet2$].[Region] AS [Region],
[Sheet2$].[State] AS [State]
FROM [Sheet2$]) b
ON [Sheet3$].[Region] = b.[Region]
The result of this is to have the polygon defined for each month you have data for a region, so month can now be used as a page.
In practice I would not include sales in the custom sql, as this means that the sales value is repeated for every polygon point for every polygon for every month for a region. However, bringing this in as a secondary source will now work correctly.
MapIssue.twbx.zip 133.5 KB
Thanks Andrew. This method will work. However, as you mentioned, this might not be practical in real world. My sales table have millions of rows, to make it 1000 time bigger might drag down the Tableau. That's why I'm looking for a way to alter the default "Demographic Role" in Tableau. I think that will solve my problem. Any other thought?
The problem is that you want to filter (or page) on something that the primary datasource (geo data) does not know about (month), but to make the geo data work as a polygon, it needs to be the primary source. Changing the Geographic role could be used, but only works to pinpoint latitude and longitudes, so will not get you a filled map.
If you alter the code to create a data source that replicates the polygons for each one of whatever you want to filter on (e.g. month), then the geosource will be bigger (say 36x bigger if you have 3 years worth of data you want to see monthly).
Don't include the sales columns, or any data like that, but use that as your secondary source.
That should allow you to get what you want without dragging down the performance of Tableau.
Thanks Again Andrew.
This will help with one extra dimension (Such as month). I have many other dimensions I want to use/show. If I can create a layer as default roles in Tableau, like a state or country, that will solve all the problems. Does Tableau allow users make changes?
I'm not sure I quite understand what you are asking, so I apologise if my answer doesn't quite fit your question.
Tableau has, built in, names of states, cities, zip codes etc with a latitude and longitude for the centre of that object. Additionally, it can do a filled map on country or US states.
Now, custom geocoding allows us to input a list of object names, with a single latitude and longitude for that item. So, for instance, the single latitude and longitude definition for the centre of a sales area.
Currently we can not add additional shapes to be plotted as filled maps unless we create custom polygons, but that brings us back to your original problem, that we cannot filter properly on a secondary data source.
The alternate I would then suggest is to consider changing the way you want to display the data. Filled maps look great, but are often misleading, and can only show one metric at a time - the size only indicates land mass, not sales performance. Additionally, the colour gradient for the scale of filled maps can make it difficult to compare different sales regions' performance.
An alternate is to use bar charts - not as fancy as a filled map, perhaps, but potentially clearer for your data.
Otherwise, you will have to use a different datasource as the geographic data part for each map that needs to be filtered/paged by a different metric.