In my previous article, we discuss how to use Qlikview to display tabular information. Now, Let's think about a scenario where we need to represent location-based sales for a company operating in India..
We can represent this information in many ways: tabular format, graphical representation or representation on geographic maps. Let's take a look at each of these representations and see why we should represent information in Geo-Map.
Some key benefits of using geospatial visualization are:
We can easily understand the distribution of the presence of organizations across the country.
Easy to represent a large number of locations compared to tabular or graphical representation
More intuitive decision making for the business owner.
Due to these benefits, there has been a marked increase in geospatial visualizations. Almost all BI tools have started offering this visualization as a standard option. (for instance, Qlikview, Painting, SAS Visual Analytics, etc.).
When I started using Qlikview a few years ago, I wanted this visualization to be part of the dashboards that I prepare, but i couldn't find a well structured tutorial about it. Therefore, I thought about contributing to this article for the benefit of other analysts like me, who are struggling with a similar question.
Steps to represent information on Google Map:
Load base data (with location latitude and longitude).
Generate variables from google maps
Create a scatter plot and set up Google Map
Loading the base data with the latitude and longitude of the location:
Below is the Excel file used for this tutorial. Upload this to Qlikview:
Qlikview Google Maps Base Data
Make sure the latitude and longitude variable names are in lowercase, since the google map variable is generated in lowercase only.
Generate Google Map Variables:
Create another tab in the script (Archive -> Edit script. Later, in a new window Tab.> Add new). Then paste the following code to generate the 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+(without((latitude)*pi()/180)))/(1-(without((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(without((latitude)*pi()/180)))/(1-(without((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 on 'Save’ and then on 'OK’ to close the window and reload the script.
Google map variable
Create a scatter chart and configure it for Google map:
To create a chart, “New sheet object” -> “Graphic” -> Follow the steps shown in the images below
Paso -10: Then, go to the SORT tab, press the back button and write the following code in the expression:
On the Title 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 a geographic map in Qlikview. Have you done something similar in the past? Do you have any experience or case studies illustrating the same in Qlikview or other visualization tools?