Sales Heatmap Issue

[jnpb]

Hi all, 

I'm trying to build a visual heatmap using data from two sources in order to visualize #of orders attributed to each location on the map. Both data sources include a common field: ( ID ) that are unique values attached to each location on the same row.
One of the data sources includes another column (OrderID) whose values are also unique, each representing an individual order. 

How do I express the number of orders to each location? I feel this should be a sum of all orders that are attached to the common (ID) field , but how do I write that?

Any insight on how to accomplish this would be enormously appreciated - thank you!

[jnpb]

This worked, and you've been a great help - thank you so much for the assistance with this!

[johncolley]

Hi @jnpb , Is your data set up like the following:

Table 1 has columns CommonID and Location - Table 2 has columns OrderID and CommonID with a relationship of one to many going from Table 1 to Table 2? 

If so you can use the Location column from Table 1 and the Count of Order ID from the second table, see example below: 

If my assumptions above are incorrect, can you please provide some sample data so I can better understand your issue. 

If this answer solves your problem please mark it as a solution!

[jnpb]

Hi @johncolley 

I've attached a screenshot of how the relationship is currently configured - I think the relationship matches what you're referring to, but when I use the LocationID from Table 1 in the visualization and the Order ID (Count) from table 2 in the Value field, it shows incorrectly on the map (also attached).

It might be worth mentioning that table 2 includes longitude and latitude fields. When i use these in the Long/Lat fields in the visualization but without including the Order ID, it correctly places the locations on the map (attached). 

However, if I add the Order ID (Count) in with the Latitude and Longitude, it breaks and shows everything in one spot in the wrong part of the world (also attached). 

Tried to cover all of the information you might need to help untangle this; I can't wrap my head around this and feel like I'm missing something really simple. 

Thanks again,
jnpbData RelationshipJust Latitude and Longitude - placement is correctLatitude , Longitude and Order ID entered

[johncolley · Accepted solution]

Hi @jnpb , Just check the two columns you have made the relationship on and make sure that all Location ID from your operation table has a matching value in the Location table. When I did not or had an error in my location table it would create a heat point near Africa. Also make sure your Latitude and Longitude fields are marked as those categories - you should be able to tell because they should have a globe symbol next to it as per my image below. You can set this with the Data Category option.

If this answer solves your problem please mark it as a solution!