If the bubble is not showing on map it means you have no data for those states.
For the color coding you can do it by >Properties>Colors and Legends>Color by Custom> By expression>
Enter your desired expression to get colors you want.
Can you share a sample date and what colors you trying to achieve with what fields?
Currently the chart is being displayed like this using custom color the expression being used is sum(Audit rating) and dimension being the City. The bubble color needs to be according the audit rating just as 1 is Yellow, 2 is Green and 3 is Red.
Further, if there are multiple data for lets suppose Greenville then the rating is being added I tried using just the Audit Rating as the expression but it is changing the size of the bubbles as below.
*I used the expression in the custom color in the expression section which is giving error.
=if(([7.Audit Rating])='1'], Yellow(),
if([7.Audit Rating]='2'], Green(),
if([7.Audit Rating]='3'], Red())))
And to adjust the bubble size
Properties>Layers>Point Layer>Below you'll find bubble size slider
Adjust that slider to adjust the bubble size.
Maybe use this one
=if([7.Audit Rating])='1', Yellow(),
if[7.Audit Rating]='2', Green(),
if[7.Audit Rating]='3', Red())))
Try the above expression for your color code, check the ] which is not needed I guess, hence you getting error I suppose.
Note: Multiple states has same audit Number....
Like FL,IL,IN has audit number 2 and if you select those states it will only show Green. For your multiple data sets, if I understood it correctly.
The color for the specific rating did work but for bubble size the bubble slider worked as well thanks for the suggestion. But for some reason some of the location for the bubble is not showing in the right location as for example Rochester, NY and also Greenville, SC.I am currently using the free cloud version of QlikSense.
Thanks so much.
I do not think there is any difference for a developer if he/she is using Qlik Sense Desktop or Cloud.
Cloud is used like an environment to host apps for other users but everything else is same, I've never used Cloud but Qlik Sense has no different function for Desktop or Cloud or Hub unless you trying to implement Section Access, which can be done only on Server. (developer point of view)
The bubbles not pointing on correct location might be cus of wrong Latitude and Longitude.
Check the Latitude and Longitude for Rochester, NY & Greenville, SC in your data and verify it with by searching on google.
Rochester, NY : 43.1610, -77.6109
Greenville, SC : 34.8526, -82.3940
Above are Lat & Long from Google search, verify it with your lat and long in data. If it is an XL file, change it. If it is coming from a source db then ask the concerned person to verify it.
Thanks for your response. The latitude and longitude used is as below. I verified in google that is the lat and long for the specified location. I see that you have used - in the second coordinate do you that should make a difference. I see in the map for all other location the bubble is at a exact location except for the sited below which is confusing.
Rochester, NY :
43.1610° N, 77.6109° W
Greenville, SC :
34.8526° N, 82.3940° W
May be make 2 new columns
And use the Latitude without ° N
And same for Longitude, without ° W. Instead of ° W use - for Longitude.
As below Image.
Example: Rochester, NY : 43.1610, -77.6109
If it a local file it won't be much of a problem to change/hardcode the values.
After you have Latitude & Longitude loaded
Use the following syntax in script....
2.Site Location - Country,
3. Site Location - City,
GeoMakePoint(Latitude,Longitude) as GeoKey
GeoMakePoint(Latitude,Longitude) as GeoKey <<<
Use GeoKey as Point Layer in your Map as Dimension.
Rochester, NY : 43.1610, 43.1610
Latitude should be with out any sign (+or-) just numbers (ex:43.1610)
Longitude should always be with - sign (ex: -77.6109)
Make 2 separate columns
State Latitude Longitude
NY 43.1610 43.1610
..... so on
The Region field should match with given Lat & Long.
Find the Sample attachment XL file for your reference. Your data should be in similar fashion.
If you do not have audit or Year in same file it is fine. You can always make a link to the Lat & Long Data
Or you can load State or Region name in other file to make a link to the Lat& Long.
In above image you check the properties
Edit>Click on the Map object>Properties will show> you'll see Points Layer as a dimension field
In below image you see is the script
I've loaded the XL file having fields
State, Latitude, Longitude, Year and audit
So I added a syntax
GeoMakePoint(Latitude,Longitude) as GeoKey,
I used GeoKey in my Points Layer as a Dimension.
Lat & Long1.xlsx 14.2 K
The map seems to be coming out correct for Rochester and Greenville but for Munich, Milan and Hastings its not coming out correct. Can you also let me know how do I attach excel sheet to the discussion forum. I really appreciate your help.Thanks.
Below is the test data and map. Please let me know if you have any question.