In my previous article, we discussed how to use Qlikview for visualization of tabular information. Now, let’s think of a scenario, where we need to represent location wise sales for a company running operations in India.

We can represent this information in many ways – tabular format, graphical representation or representation on geographical maps. Let’s have a look at each of these representations and see why should we represent information on Geo-Map?

A few key benefits of using geo-spatial visualization are:

- We can easily understand the distribution of Organizations presence across the country.
- Easy to represent high number of locations compare to tabular or graphical representation
- More intuitive decision making for the business owner.

Due to these benefits, there has been a marked increase in geo-spatial visualizations. Almost all BI tools have started offering this visualization as a standard option (e.g. Qlikview, Tableau, SAS Visual Analytics etc.).

When I started using Qlikview a years back, I wanted this visualization to be part of the dashboards I prepare, but i was unable to find a nicely structured tutorial around it. Hence I thought to contribute this article for the benefit of other analysts like me, who are struggling with similar question.

**Steps to represent information on Google Map:**- Load base data (with Latitude and Longitude of location).
- Generate Google Map Variables
- Create a Scatter plot and configure Google Map

**Loading the base data with the Latitude and Longitude of Location:**Following is the excel file used for this tutorial. Load this in Qlikview:

Make sure that Latitude and Longitude variable names are in lower case, as google map variable gets generated based on lower case only.

**Generate Google map variables:**Create another tab in script (File -> Edit Script. Then in new window Tab .> Add New). The paste the following code to generate Google Map variable.

// Google Maps Key

// get a key here http://code.google.com/apis/maps/signup.html

gmap_key = 'ABQIAAAAPTYGDOAE5g8mVF5kIaY54RSHwNu1JdqUbdjDP-YY4oym4d2mTxSRdsISI-1rwwnl-9gjJrMO3oTkMA'; max_zoom_level = 5; //maximum value 17 def_map_size = 400; // Variables required for calculating map // No need to change these var_pi180= '=pi()/180'; var_lat_offset= '0'; var_mc2= '=256*pow(2,$(var_zoom))'; var_mc1= '=256*pow(2,($(var_zoom)-1))'; var_mid_lat= '=min(latitude)+(1+var_lat_offset)*((max(latitude)-min(latitude))/2)'; var_mid_long= '=min(longitude)+(max(longitude)-min(longitude))/2'; var_zoom= '=max(aggr(if(max( round(256*pow(2,(_zoom_level -1)))+( longitude *((256*pow(2,_zoom_level ))/360)) )-min( round(256*pow(2,(_zoom_level -1)))+( longitude *((256*pow(2,_zoom_level ))/360)) ) <map_size_x AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<map_size_y,_zoom_level,1),_zoom_level))'; var_maptype= '=if(isnull(only(maptype)),fieldvalue( '&chr(39)&'maptype'&chr(39)&', 1 ),maptype)'; map_size_x= '400'; map_size_y= '400'; SET HidePrefix='_' ; // Field required for calcualting best zoom level _zoom_level: Load RecNo( ) as _zoom_level autogenerate(max_zoom_level); maptype: LOAD * INLINE [maptype roadmap mobile satellite terrain hybrid ];

After pasting the code, click ‘Save’ and then ‘Ok’ to close the Window and reload the script.

**Create a Scatter Chart and configure for Google map:**To create chart, “New Sheet Object” –> “Chart” –> Follow the steps shown in below pictures

**Step -10: Next, move to SORT tab, press back button and write following code in expression:**

**“=SUM( round (256*pow(2,($(var_zoom)-1)))+( longitude *((256*pow(2,$(var_zoom)))/360)) )”**

longitude:

longitude:

**latitude: **“=SUM(((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))))”

**Step -11: Add two more expressions: City and Sales
**

**City:**= “City”

**Sales:** “=sum(Sales)”

**Copy and paste following code in step 21 and 24 **

**X axis: **

**s**tatic Min: =(256*pow(2,(** $(var_zoom)**-1)))+(

***((256*pow(2,**

*var_mid_long***))/360)) -round(**

*$(var_zoom)***/2)**

*map_size_x*Static Max: =( (256*pow(2,(** $(var_zoom)**-1)))+(

***((256*pow(2,**

*var_mid_long***))/360)) + round(**

*$(var_zoom)***/2))**

*map_size_x***Y axis: **

Static Min: =((256*pow(2,(** $(var_zoom)**-1)))+((0.5*log((1+(sin(

***pi()/180)))/(1-(sin(**

*var_mid_lat****pi()/180)))))*((-256*pow(2,**

*var_mid_lat***))/(2*pi())))+round(**

*$(var_zoom)***/2))**

*map_size_y*Static Max: =((256*pow(2,(** $(var_zoom)**-1)))+((0.5*log((1+(sin(

***pi()/180)))/(1-(sin(**

*var_mid_lat****pi()/180)))))*((-256*pow(2,**

*var_mid_lat***))/(2*pi())))-round(**

*$(var_zoom)***/2))**

*map_size_y*

**Copy and Paste following code in Step-26:**

='http://maps.google.com/staticmap?center='

& num(, '##############', '.', ',' ) & ',' & num(var_mid_lat, '##############', '.', ',' ) & '&zoom=$(var_zoom)' & '&maptype='&var_mid_long& '&size='&var_maptype&'x'&map_size_x& '&key='&gmap_key & '&.jpg'map_size_y

In Caption tab, width and height according to map_size_x and map_size_y (defined in the script).

**Finally, we get the desired result**s:

This is an example where we represent information on geographical map in Qlikview. Have you done something similar in past? Do you have any experience / case studies illustrating the same in Qlikview or other visualization tools.

Do share your thoughts through comments below.

This is quite an interesting way to represent data!

Is there a way to add the legend value as well? Also, believe there is a constraint of plotting only a single variable at a time. Or can we plot multiple?

Nice article.

Note, that in some cases “Google Map key” problem could raise.

Then better to use this link in Dynamic Image

=’http://maps.google.com/maps/api/staticmap?center=’

&

num(var_mid_lat, ‘##############’, ‘.’, ‘,’ )

&

‘,’

&

num(var_mid_long, ‘##############’, ‘.’, ‘,’ )

&

‘&zoom=$(var_zoom)’

&

‘&maptype=’&var_maptype

&

‘&size=’&map_size_x&’x’&map_size_y

&

‘&sensor=false’

Yes, you are right Andrei Kaliahin.

Hi Sunil,

Nice Article and m able to replicate it..Just curious.Could u please shed some light on the working of code?

This is a very basic feature in Tableau..but i heard it has been simplified with extensions in Qlikview now ..is that true?..any better in Qliksense version?