Right-click on the pie chart and bring up its Properties. Navigate to the Caption tab on the right. You may need to use the left < > right arrows to see it.

Figure 9-2: Caption tab: Allow Minimize and Allow Maximize checkboxes

Under the Caption tab, you will see the Allow Minimize and Allow Maximize checkboxes on the bottom-right corner. Check them both, click Apply, and close the wizard. Now you will see the standard computer display symbols for minimizing a window.

Figure 9-3: Minimize and maximize symbols now appear

Click the - symbol to minimize the pie chart and make room to spread out the line chart. Click on the words in the minimized box, and the pie chart will maximize. From the maximized chart, you can choose the - symbol again to minimize. You can also use the double-boxes symbol to restore the pie chart to its original position.

Next, we will work on the Properties of the line chart so we can find out what it represents and how we can improve its display. Right-click on the, Expense as % Sales line chart, choose Properties, and navigate to the Layout tab. Check the checkbox to Allow MoveSize so that we can make the image wider and see the whole label. Next, move to the Expressions tab. We can see that the expression is labelled 2010 Expenses but the actual formula is as follows:

Sum({<[Fiscal Year]={$(vCurrentYear)}>}ExpenseActual)/Sum({<[Fiscal Year]={$(vCurrentYear)}>}[Sales Amount])

In the preceding formula, the expenses for the currently selected Fiscal Year are being divided by the sales amount of the currently selected Fiscal Year. How are we getting two lines from one expression? It is also set to calculate a trend line as a polynomial of the second degree. So, the orange line is our actual Expenses as a % of Sales, and the turquoise blue line is the trend line.

Now, in the same tab, click on the actual label, 2010 Expenses, and use the ellipse button (…) to the right to bring up the label properties. Click on the Functions tab in the label properties. Here you will see that the label actually has a function attached to it:

dual Above ([TOTAL] expr [, offset=1[, count=1]])

Figure 9-4: Function in the Edit Expression wizard

What does that do? Actually, this function is created automatically and is commonly used throughout the CFO example dashboard in chart objects; we have just not looked at it before. The expression dual ( s , x ) forces the string s (in this formula, s represents Above ([TOTAL] expr) to be associated with a number represented by the x in the expression (in this expression it is the count of 1).

This expression can be leveraged in both scripts and chart expressions. This is often used with dates so that December 2009, December 2010, and December 2012 will be displayed as DEC. The dual function will be used early in a given load script or expression, before the other data is loaded, so that the first string (such as DEC in this example) will be shown in the list box using the dual function.

Another QlikView expression function that can be used is Expr. It evaluates, in the expression where it is used to the value to look for in the specific field or cell of the loaded table data. So, in the expression associated with our Expression field, Expr evaluates to whatever is located at the offset of one.

Changing the given expression to above([ total ] expr [ , offset [,n ]]) tells QlikView to return the value of the expression from the row above the current row within a column; the offset is calculated by QlikView from the preceding function when calculating a chart's straight table value.

If the chart is one-dimensional, or if the expression is preceded by the [Total] command, then the current value is always going to be equal to the aggregate of the entire column.

Now that we've understood that the function is telling the chart to use the totals of the expression for each intersection of the month, close Properties and stretch out the chart so that we can read the entire key.

Suppose we want to change the key so that it is color-coded to match the line colors that were chosen. For this, perform the following steps:

Review your changes. They should look similar to what is shown in the following screenshot:

Figure 9-5: Changed line chart with the key color-coded

Now we will examine the bar chart on the upper-left side of the sheet.

Most of what we will discuss in this section will be a review of the earlier chapters. For example, the first expression Label formula is ='Expenses '&$(vCurrentYear), which tells QlikView to show the actual year selected, with the word Expenses preceding it. The actual expression formula for the first Expression is:

Sum({<[Fiscal Year]={$(vCurrentYear)}>}ExpenseActual)/1000

This tells QlikView to display the sum of the ExpenseActual data field divided by one thousand. The result is displayed in thousands, as indicated by the chart label $000.

The second formula Label is 'Expenses '&$(=(vCurrentYear)-1), which tells QlikView to get the previous year and display that with the word Expenses preceding it. The actual expression formula for the second expression is as follows:

Sum({<[Fiscal Year]={$(=(vCurrentYear)-1)}>}ExpenseActual)/1000

This tells QlikView to get the previous year and to display the sum of the ExpenseActual data field divided by one thousand. We have reviewed both of these formula patterns before.

The third and final Expression just uses the Budget data and the formula Sum(ExpenseBudget)/1000. The squares on the Budget line are produced by using the Display Options section of the Expressions tab.

Figure 9-6: Display options used to set budget line

The Symbol checkbox, is checked and the option Squares Filled is selected. The Line checkbox is checked to instruct QlikView to use a line instead of a third color bar. The Expressions as Legend checkbox is also checked, instructing QlikView to put the legend key across the bottom with the Label names of the expressions.

The pivot table variances are calculated in the expressions with those labels; as we surmised, the text is color-coded by using the Visual Cues tab of the Properties. Open the Pivot Table Properties to examine the Expressions tab. Interestingly, rather than calculating just on the basis of other fields, some of the calculations use the column designation (similar to the Excel expression A1-B1), and carry it down the column. For example, Budget Var is the expression column(1) – Budget, and Budget Var % is the expression column(3)/column(2).

Next, navigate to the Style tab. In the upper left-hand corner, you will see the Current Style selection. The drop-down shows thirteen different styles to choose from. Pick a new one, and choose Apply to see your style changes.

If we don't have drill-down details loaded to our QlikView document, perhaps we could put in a link that would take us to an intra-company website with the additional details on the expenses that we want. We can link Field Values to Links by creating a Link Expression in the Definition field of an Expression. The Definition of the Expression needs to be in the following format: Text_to_display&<url>Text_of_url_link.

Text_to_Display will be displayed in the table cell. Text_of_url_link will be the actual URL link that is opened in a new browser window. To create a clickable link in our Pivot Table, follow these steps:

Now the column named MyLink shows at the end of the pivot table. Under the header, the URL command and its path are displayed. Click on one of the Link fields; QlikView will ask you if you want to open the link.

Links can be web pages from your internal website or the World Wide Web. They can be local files from your personal computer or files located on a company-shared folder, images, or even sounds files. (For example, I really wanted to add the sound of a cow mooing to Chapter 7, QlikView Inventory Analysis when we were investigating the Dairy Product Group.)

You can create separate load files in Excel describing which information files are to be linked to each field value, and then you can tell QlikView to treat the tables created from the load files as information tables. Set up information tables with two columns. The first column should be Field Name and contain a list of values associated with that field. The second column can be titled with a name of your choice; it will contain the information itself or references to non-text files such as pictures or applications (such as Word and Excel).

If we load images as links, they will open in a separate viewer. But we might just like to add some custom images to a QlikView document. To see how you can do this, reopen your first QlikView document, CheyeneCo.qvw, used in Chapter 1, Getting That Financial Data into QlikView. You can practice by adding your own images to your document.

Perform the following steps to add images to the Main tab:

QlikView comes preloaded with some image files and sound files. These image and sound files can be used in your QlikView application directly, without loading them. We can refer to them within an expression or a file load script.