Concat aggregation sort not working for google chart map

Tue, 24 Jul 2012 10:22:58 GMT

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

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

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

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

Re: Concat aggregation sort not working for google chart map

Thu, 09 Aug 2012 13:41:18 GMT

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....

topic Re: Concat aggregation sort not working for google chart map in QlikView

Concat aggregation sort not working for google chart map

Re: Concat aggregation sort not working for google chart map