The KPIs tab has very good display choices. The months and years are readily visible, inviting selections. The use of the red/green stoplight combined with the slider bar actually functions as a selection box to change the bar graph. So, when selecting a specific KPI, we immediately see whether or not the measure is above or below budget. If our CFO happens to be red/green color-blind, we might want to switch our stoplight colors to blue and yellow or other alternating pictures (although happy faces and frowning faces are probably considered juvenile). The budget data is only loaded for 2011 for demonstration purposes.
Click on Expenses: % of Sales, and you will notice that the bar chart adds a red line graph to represent the current year's (2011) budget. Other than that special case, the bar chart either just displays the KPI by month or displays the KPI and the budgeted KPI.
Across the top of sheet, we have three text objects. The first one is formatted to show the title of the page: Key Performance Indicators. By right-clicking on the first text object, we can choose Properties and examine the properties that can be set for the text object. Each Text Object Properties wizard has five tabs:
In the case of the text object title, the only properties that are set are on the General tab and the Font tab. The General tab contains the foreground text to display and the background color. The Font tab has the font display choices. We would use the Layout tab to create borders around the text object, if desired.
The second text object contains instructions and, like the title, just uses the General tab and the Font tab.
The third text object in the first row of objects is a little more interesting. It looks like a button and, indeed, if clicked on, it has an action—to clear all the selections. Look at the Properties of the text object that looks like a button that says Clear All. On the very first tab, the General tab, we can see that it is indeed a picture instead of text such as the title. This particular picture even has the words Clear All right in it. We just learned that text objects can actually contain pictures. An actual Button object could have been used instead of the text object with Actions. Either object functions the same way based on the Actions associated with it.
Several kinds of picture formats can be used: bitmaps (.bmp
), jpegs (.jpg
or .jpeg
), graphic interchange formats (.gif
), and portable network graphics (.png
) will be the types listed if you click on the radio button for Image and then the Change… button to choose an image.
On the Actions tab, there is a box that shows what Action is selected, if any. This particular instance of text object has the Clear All action selected. If you click on the Add button below the box, then the types of actions are displayed on the left-hand side. And when you select one of the action types, the different actions associated with that Action type are displayed on the right-hand side to be selected.
There are five action types for text objects:
One Selection action that probably needs further explanation is Pareto Select. To make a pareto selection in the specified field, it should be based on an expression and a percentage. This type of selection is used to select the top contributors to a measure, typically defaulting to a general 80/20 rule. For example, to find the top sales outlet that contributes to 80 percent of the sales, Sales Outlet should be used as the field, sum (Sales) should be used as the expression, and 80 should be used as the percentage.
To switch to web mode, navigate to the View menu at the top of the QlikView interface and then scroll down the View menu popup to choose the Turn on/off WebView toggle. For now, we will work with WebView off. Working with WebView to create objects requires drag-and-drop techniques, and they will not be covered in this book. If you are interested in learning more about objects in WebView, please see my previous book about QlikView: Instant QlikView Application Development, also published by Packt Publishing.
The use of External actions is highly dependent on how much QlikView is allowed to interact with the environment. A few clients will not handle these settings and most External actions only function in WebView or when connected to QlikView server. Some run only with the proprietary QlikView Server Ajax ZFC client, and others will not run with the Ajax client. If your QlikView application for your CFO or other C level officer is going to need one of these specific functionalities, please review the requirements in QlikView help, and coordinate with your technology department for application and environment support.
The actions available from the External actions menu are explained next:
It has parameters to be specified, such as:
In the second row of sheet objects, we find alternating text objects to describe the list boxes. So, the words Filter by Year are in a text object, and the actual years that can be selected are in a List Box displayed as 2009|2010|2011 and available for selection. Next comes another text object that identifies that the next List Box contains Month for selection. What we have failed to notice is that there is actually a sheet object between the first row of text and the second row of text. If you right-click the pale-blue line in between and choose Properties, you will see that it is a Line/Arrow object [] with three linked objects. Using Properties in the General tab, experiment with making Line/Arrow objects appear in different colors.
What looks like a third row of sheet objects is actually an interconnected set of text objects with Actions and layered charts, along with a block of layered, invisible charts made visible by Set Variable Action. So, when we click on a row with the stoplight circle, we are actually selecting a text object and that, in turn, makes the associated blue bar chart visible. The first stoplight row, Revenue vs Budget , is text object TX103
and passes the vShowChart variable set to a value of 1. This makes chart object CH54
visible because, in the Layout tab of the Properties chart, the Show section picks up the vShowChart variable when the value is 1. The next stoplight row in the table-like structure uses the same variable vShowChart (as does each of the other rows), but the variable is set to equal 2 (or 3, 4, and so on) to control its associated chart.
The Layout tab of Properties also contains the Layer designation. There are three standard Layer designations—Top, Normal, and Bottom—corresponding to the internally numbered layers 1, 0, and -1, respectively. Custom layer values between -128 and 127 are accepted. Choose Custom to enter a value of your choice.
An additional chart object is layered under the rows of Text objects. For each row of the table-like structure, they use images to display the appropriate red or green circle images as an Image Chart object. Next in Chart object, Text Chart object is used to display the percentage. Finally, to get the bar line, Linear Gauge is used. These are all a single chart object with multiple displays. For example, for the last text object row in the KPIs measures block—Inventory-Turns per Year—the three charts in the chart object are set up in the following manner:
On the Dimension Limits tab, the dimension used is this formula:
=If([Dashboard Metric]='AR: Day Sales O/S' or [Dashboard Metric]='Inventory - Turns p77781er Year',[Dashboard Metric])
Then, on the Expressions tab, we have three representations of the same information. The first is Image, the second Text, and the third Linear Gauge:
Only the stoplight image chart has a definition entered on the Expressions tab. In the previous image, we can see the tail end of the expression definition with eight parentheses. The definition is an expression that tells the chart to flip between the red and green images. This is the definition code used for the Inventory – Turns per Year:
If([Dashboard Metric]='Inventory - Turns per Year', If((Sum(ThroughputQty*CostPrice)/Sum(StockOH*CostPrice))>10,'qmem://<bundled>/BuiltIn/led_g.png', If((Sum(ThroughputQty*CostPrice)/Sum(StockOH*CostPrice))<=10 and (Sum(ThroughputQty*CostPrice)/Sum(StockOH*CostPrice))>5,'qmem://<bundled>/BuiltIn/led_g.png', If((Sum(ThroughputQty*CostPrice)/Sum(StockOH*CostPrice))<=5,'qmem://<bundled>/BuiltIn/led_g.png'))) ))))))))
The parts of the code line in single quotes with the extension .png
are the image files and their paths. We will create our own image display in Chapter 9, QlikView Expenses Dashboard.
The following is the load script used with the KPIs dashboard tab. It has the standard English language settings for date and time formats and thousands separator. If we had chosen a different language, other standard settings would be part of the script. The very first line with the word Binary
is loading data and dimensions from another QlikView document. It must come first in the script and loads very fast. As you read more of the script, you can also notice that, for function commands such as SET
, LET
, and NUM
, the script is case-insensitive:
Binary [executive dashboard.qvw]; SET ThousandSep=','; SET DecimalSep='.'; SET MoneyThousandSep=','; SET MoneyDecimalSep='.'; SET MoneyFormat='$#,##0.00;($#,##0.00)'; SET TimeFormat='h:mm:ss TT'; SET DateFormat='M/D/YYYY'; SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'; SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; LET vCurrentMonthNum = NUM(Month(Makedate(Year(Today()),5,31))); LET vCurrentMonth = Month(Makedate(Year(Today()),5,31)); Let vCurrentYear = num(year(today())); Let vTodaysDate = num(today()); Let v12month = num(Makedate(Year(Today())-1,5,31)); Let vRolling12months = Num#($(v12month),'YYYYMM'), LET vOldDate = num(Makedate(2003,5,31)); LET vTodaysDate = num(Makedate(Year(Today()),5,31)); LET vToday = (Makedate(Year(Today()),5,31)); LET vAdjDays = $(vTodaysDate) - $(vOldDate); //the gap between july 2003 and today LOAD * INLINE [ Display as Dollars Percentage ]; [Dasboard Metrics]: LOAD * INLINE [ Dashboard Metric Revenue vs Budget Revenue vs Last Year Margin vs Last Year Margin % Revenue Expenses: % of Sales AR: % Overdue AR: Day Sales O/S Inventory - Turns per Year ];
After the language-related standards are defined with SET
statements, we use LET
statements to define global variables, such as the current date, current month, and current year. SET
statements assign a specific value to a variable. LET
statements use expressions to calculate based on the values assigned to the variables within the expression. That is why LET
statements should come before SET
statements in your script.
Following the LET
statements, we can see that the data is loaded INLINE
. This means that we will type the data into the script and not load it from an Excel or other file. We use the Inline Data Wizard for help with the creation of LOAD INLINE
statements.
The Inline Data Wizard dialog is opened from the menu path:
File | Edit Script | Insert menu | Load Statement | Load Inline
This wizard is used to create the load inline statements in the script. The following screenshot shows this:
The wizard contains something that looks similar to a spreadsheet and, in fact, works much like one. However, calculation formulas will not be evaluated in this spreadsheet as they would in Excel.
Each column represents a field to be loaded into QlikView in a manner similar to the Excel spreadsheets we used in Chapter 1, Getting That Financial Data into QlikView and Chapter 2, QlikView Dashboard Financial KPIs. Just as with the Excel spreadsheets we used, each row is a record in the table. A data cell is selected by clicking on it. A value can then be typed or pasted from the clipboard. Just as with Excel, press Enter or any arrow key to accept the value and move to another cell.
The top (label) row of the inline wizard data entry form is reserved for the dimension labels. Double-click in a label cell to enter a name. If no custom labels are entered in the label row, the field names F1
, F2
, and so on will be used.