Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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','')
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)
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.