Creating a simple and effective Sales dashboard (with Qlikview) – Part 2

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.

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.

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.

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. 

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). 

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

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.

Once 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).

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())
[/stextbox]

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())) 
[/stextbox]

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