3 Replies Latest reply: Apr 12, 2013 5:20 PM by Eddy Sanchez RSS

    Blank state names in Google Maps

    Rebecca Molstad

      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.

        • Re: Blank state names in Google Maps
          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','')

            • Re: Blank state names in Google Maps
              Rebecca Molstad

              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.

            • Re: Blank state names in Google Maps
              Eddy Sanchez

              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)