Concat aggregation sort not working for google chart map

[#1]

Hi Guys,

I'm trying to make an amendment to the google chart API from Rob's cookbook.

I have the code;

='http://chart.apis.google.com/chart?&cht=map' & '&chs=600x480' /* Chart size */

& '&chld=' & concat(DISTINCT 'GB-'& State, '|', text(fieldIndex('State', State)-1))

& '&chdl=' & concat(DISTINCT County & ' Sales:' &  aggr(Sum(CountyValue),County), '|', text(fieldIndex('State', State)-1))  /* Legend */

& '&chco=' & concat(if(aggr(Sum(CountyValue),State)>=200,'336600','FF0033'), '|', text(fieldIndex('State', State)-1))

& '&chtt=Nicks+Google+API+Map+Chart'  /* Chart Title */ //This works the title

& '&chm=' & concat(DISTINCT 'f' & County & ' Sales:' &  aggr(Sum(CountyValue),County) & ',000000,0,' & text(fieldIndex('State', State)-1) & ',15', '|', text(fieldIndex('State', State)-1))  /* Chart Markers */

& '&chma=10,110,10,10' /* Chart margins */

within a chart which is displaying the map and counties correctly. If i check the url code output it shows;

http://chart.apis.google.com/chart?&cht=map&chs=600x480

&chld=GB-BDF|GB-CAM|GB-ESS|GB-HRT|GB-LUT|GB-NFK|GB-PTE|GB-SFK&chdl=Bedford Sales:43.85|Cambridgeshire Sales:717.52|Essex Sales:4352.59|Hertfordshire Sales:1199.21|Luton Sales:66.01|Norfolk Sales:0|Peterborough Sales:522|Suffolk Sales:143.4

&chco=336600|336600|336600|336600|FF0033|FF0033|FF0033|FF0033

&chtt=Nicks+Google+API+Map+Chart

&chm=fBedford Sales:43.85,000000,0,8,15|fCambridgeshire Sales:717.52,000000,0,27,15|fEssex Sales:4352.59,000000,0,62,15|fHertfordshire Sales:1199.21,000000,0,80,15|fLuton Sales:66.01,000000,0,103,15|fNorfolk Sales:0,000000,0,117,15|fPeterborough Sales:522,000000,0,134,15|fSuffolk Sales:143.4,000000,0,171,15&chma=10,110,10,10

It's putting the field "State" and the field "County" in alphabetical order as expected for the "&chld" and "&chdl" sections, however, the "&chco=" section seems to be sorted incorrectly.

& '&chco=' & concat(if(aggr(Sum(CountyValue),State)>=200,'336600','FF0033'), '|', text(fieldIndex('State', State)-1))

What it should be doing is summing the countyValue by state and if the value is greater than 200 then '336600' otherwise 'FF0033'. Judging by the output;

County	aggr(Sum(Distinct CountyValue),State)	concat(if(aggr(Sum(Distinct CountyValue),State)>=200,'336600','FF0033'), '|', text(fieldIndex('State', State)-1))
		336600|336600|336600|336600|FF0033|FF0033|FF0033|FF0033
Norfolk	0	FF0033
Bedford	43.85	FF0033
Luton	66.01	FF0033
Suffolk	143.4	FF0033
Peterborough	522	336600
Cambridgeshire	717.52	336600
Hertfordshire	1199.21	336600
Essex	4352.59	336600

it seems to be applying a different sort than the other aggr() functions are, even though the sort criteria is the same "text(fieldIndex('State', State)-1)".

Does anyone know how to do this so that the sort is the same as the other sections?

Regards,

Nick

[#2 · Accepted solution]

For those interested, the error made was missing the first colour in the &chco section. From the info on;

https://developers.google.com/chart/image/docs/gallery/new_map_charts#gcharts_shape_markers

the very first colour needs to be the unselected land. Once i amended the code to;

='http://chart.apis.google.com/chart?&cht=map' & '&chs=600x480' /* Chart size */

& '&chld=' & concat(DISTINCT 'GB-'& State, '|', text(fieldIndex('State', State)-1))

& '&chdl=' & concat(DISTINCT County & ' Sales:' &  aggr(Sum(CountyValue),County), '|', text(fieldIndex('State', State)-1))  /* Legend */

& '&chco=B3BCC0|' & concat(if(aggr(Sum(CountyValue),State)>=200,'336600','FF0033'), '|', fieldIndex('State', State)-1)

& '&chtt=Nicks+Google+API+Map+Chart'  /* Chart Title */ //This works the title

It worked as required.

Now, if i could only get the labels to work....