Kunal Jain — May 19, 2013
Beginner Business Intelligence Qlikview

In my last post, I discussed how a simple dashboard can provide information effectively. I will continue from where I left and go through the steps involved in creating the dashboard.

Step 1: Import data from source 

In case the data source is excel (which is the case here), go to File -> Edit Script or Ctrl + E. Once the script page opens, go to Table Files.

qlikview excel import

In case you want to import data through databases, you can connect through ODBC or OLE DB connectivity provided on left of “Table Files”.

Once you select the file from desired location, following code will be added on the script window:

LOAD City, 
     Region, 
     business_sourced, 
     rejections
FROM
C:\Users\Kunal\Desktop\temp_qlikview.xlsx
(ooxml, embedded labels, table is Sheet1);

Once the script is written with correct location, Save the script and Reload it. Once the script gets reloaded, Qlikview will open up a dialog box showing list of fields which have been loaded in Qlikview. Check that all the fields you wanted (City, Region, business_sourced and Rejections) are mentioned in this window. Close this window after checking the fields.

Step 2: Selecting the chart 

Next, right click on Main sheet and select New sheet object -> Chart….  In the next screen, select block chart from the list of charts, add any Windows title you want to add (Business Volume)  and click on Next. 

Step 3: Create a location based drilldown group

Select Edit Groups on the next screen. A window for Groups will open up. In this window, select New. On the next Window which opens, enter Group Name (Location Hierarchy), Used Fields (in order of hierarchy) and the type of group (Drill-down). 

defining new group qlikview

Click on OK a couple of times to come out to Dimensions window. Add Location Hierarchy in Used Dimensions.

Step 3: Add expressions and background colour based on second dimension

When you click Next, window for Edit Expression opens up. This is the expression which determines sizes of box in your chart. Enter sum(business_sourced) here.

expression box chart qlikviewOnce you press OK, Window for expressions opens up, with definition provided in last window. Add desired Label (Business Volume) and expand the parameters (as on next screen).

background colour qlikview

Next, you can select Background color and click on Definition. In this window, you can define the parameter for defining colors of the boxes in your chart. Insert the following code in the expression tab:

if(sum(rejections)/sum(business_sourced) < 0.06, green(), red())

This expression would color the box red when there are rejections more than 6%, otherwise they will be colored green.

If you want to add gradients of green and red color, the code can be changed to something like this:

if(sum(rejections)/sum(business_sourced) < 0.06, 
colormix1(sum(rejections)/sum(business_sourced)*10,green(), lightgreen()),
colormix1(sum(rejections)/sum(business_sourced)*5,lightred(), red())) 

This expression would color the boxes in gradient between green and light green or red and light red. Next, you can click Finish to see your chart.

This simple tutorial creates an effective dashboard with drill down and colors showing quality of business. Hope you would have found the tutorial helpful.

In case of any questions, please feel free to reach out to me. Also, let me know in case you apply these ideas to some other location.

 

If you like what you just read & want to continue your analytics learning, subscribe to our emails or like our facebook page

About the Author

Kunal Jain

Kunal is a post graduate from IIT Bombay in Aerospace Engineering. He has spent more than 10 years in field of Data Science. His work experience ranges from mature markets like UK to a developing market like India. During this period he has lead teams of various sizes and has worked on various tools like SAS, SPSS, Qlikview, R, Python and Matlab.

Our Top Authors

Download Analytics Vidhya App for the Latest blog/Article

6 thoughts on "Creating a simple and effective Sales dashboard (with Qlikview) – Part 2"

Arpan
Arpan says: October 30, 2013 at 6:36 am
Hi Kunal, I want to create a drill down dashboard in QlikView. For each level there is a separate table in SQL which is the source. The hierarchy is as follows: WW->Region->Sub-Region->Manager->Sales Rep->Transaction. Can you please help me. Thanks. Reply
Kunal Jain
Kunal Jain says: November 01, 2013 at 4:23 pm
Arpan, If you open the data sources and link them by key, you would be able to define them in a hierarchy. To create a hierarchy, you need to define this as a drill down group. Let me know if you have any specific question. Thanks, Kunal Reply
Arpan
Arpan says: November 02, 2013 at 1:14 pm
Hi Kunal, Thanks for the reply. I did not understand what do you mean by linking by key. Could you please elaborate? Reply
Kunal Jain
Kunal Jain says: November 04, 2013 at 8:59 pm
Arpan, When you import data in qlikview, it will by default find the joins and keys between tables. If it does not (or in case it joins through an artifical key), provide the right joins. After this define the hierarchy. The tutorials available on Qlikview website can take you through this step by step. Thanks, Kunal Reply
Arpan
Arpan says: November 05, 2013 at 3:47 am
Hi Kunal, Linking through the primary key is clear and I have done that. But how to define the hierarchy is all what I want to know? Can you guide me to some dashboard which I can refer for defining the hierarchy? Regards, Arpan Reply
Hossein
Hossein says: September 08, 2014 at 7:54 am
Hi i have question How i can set colors at gauge? Reply

Leave a Reply Your email address will not be published. Required fields are marked *