Blank state names in Google Maps

[#1]

Hi All,

I am working with Rob Wunderlich's Google Maps document (great work, Rob!).  However, the problem I am running into is when there are sales with no State associated.  I would love to say our data is clean enough that this never happens, but of course we have a good percentage of data where this is indeed the case.  As long as every selection has a State associated, the map works great, but when a State is blank the map doesn't run. 

I am betting that I can load only the data where there is a State name associated as a new field and reference that in the map, but I would prefer to avoid this route.  Does anyone have any other ideas?  I have attached a modified version of Rob's document to show my issue.  I'm hoping there is some easy and obvious fix that I am missing.

[Gysbert_Wassenaar]

I won't pretend to understand why, but removing the H from chd seems to work:

='http://chart.apis.google.com/chart?&cht=t'

                    & '&chs=' & '440x220' /* Chart size */

                    & '&chtm=' & 'usa'          /* Region */

                    & '&chco=' & 'f5f5f5'  /* Color for no value */

                    & ',' & 'edf0d4,6c9642,13390a'  /* Color gradient */

                    & '&chld=' & concat(DISTINCT State, '', fieldIndex('State', State))

                    & '&chd=s:' & replace(concat(  aggr(  mid('$(simpleEncodingMap)', 1+round(61*sum(Sales)/max(TOTAL aggr(DISTINCT sum(Sales), State)),1),1), State), '', fieldIndex('State', State)),'H','')

[eddysanchez]

Rebecca, the parameters of chd must to be s:??????, when the number of ? are the number of states

The problem is that the blank State generate one more value (H)

Gysbert, the replace H by empty can generate mistakes if another value was H

better is something like that:

='http://chart.apis.google.com/chart?&cht=t'

                    & '&chs=' & '440x220' /* Chart size */

                    & '&chtm=' & 'usa'          /* Region */

                    & '&chco=' & 'f5f5f5'  /* Color for no value */

                    & ',' & 'edf0d4,6c9642,13390a'  /* Color gradient */

                    & '&chld=' & concat(DISTINCT State, '', fieldIndex('State', State))

                    & '&chd=s:' & left(concat(  aggr(  mid('$(simpleEncodingMap)', 1+round(61*sum(Sales)/max(TOTAL aggr(DISTINCT sum(Sales), State)),1),1), State), '', fieldIndex('State', State)),count(DISTINCT State)-1)

[#4]

That seemed to do the trick for the sample file but still nothing for my actual file.  The latest development is that I have also found a couple instances where there aren't any states missing and the map still won't respond.  I have narrowed it down to being a problem with the chd as you suggested, but no idea why some seem to work and others don't...  I will try to put together a better sample file that gets closer to my data.