Qlikview for Finance: Concoct dynamic business intelligence dashboards for financial analysis with QlikView

Start QlikView from your Start program menu in Windows. After QlikView starts, you can begin to navigate around in it and start to familiarize yourself with the environment.

QlikView consists of two parts: the sheet contains sheet objects, such as charts or list boxes that show clickable information, and the load script stores information about the data and the data sources that the data is coming from.

Financial professionals always use Excel to examine their data. So, we are going to start by loading data from an Excel sheet using the New document button. QlikView can also help you create a basic document sheet containing a chart. The newest version of QlikView comes with sample Sales Order data that can be used to investigate and create sheet objects.

To use data from other file types, you can use File Wizard: Type that you start from the Edit Script dialog by clicking on the Table Files button.

Using the Edit Script dialog, you can view your data script, edit the script, and add other data sources. You reload your data by clicking on the Reload button. If you just want to analyze data from an existing QlikView file and analyze the information in it, you do not need to work with the script at all.

After QlikView opens, you will see the default start screen:

Figure 1-1: The default start screen

Here, you will see the side menu with Getting Started ► as the highlighted selection; along with Recent, which will be blank the first time QlikView is opened; Favorites, which is also empty at this point; Open in Server, which allows you to register the QlikView server if your company has an Enterprise QlikView license and is running QlikView from a server; and Resources, where you can follow links back to the QlikView website and see more examples. In the lower-left corner, you have the option to uncheck the box to show this screen when starting QlikView. Leave that option checked to make it easy to navigate to examples and training.

Getting Started shows that you are running QlikView Personal Edition, with options to use Learn QlikView and to see Examples. The third entry, under Examples in this version, is Data Visualization, which is a very good tutorial on what types of charts to use to aid in visual discovery and understanding of your data relationships.

Now, let's create our first QlikView document by loading data from Excel. Start by clicking on the New Document button located in the lower-right corner of this version of QlikView. Prior editions of the personal download have other options, such as a link under Learn QlikView saying Create a new QlikView document by loading data from an Excel file.

For this example, we will use sample financial data downloaded from an ERP system into Excel. This sample data can be downloaded from your account at Packt Publishing at http://www.PacktPub.com. The filenames are CHData-Oct.xls and CHData-Nov.xls. Review the data in Excel before you load it to learn how a load file should be set up.

There are six steps to setting up the load file:

We have now created our first QlikView application. Move your objects around so that your table box is lower in the screen, and widen it so that all the columns can be seen. Then, in the free space above, right-click and add a statistics box. I have named it Statistics Box in the screenshot. Make sure that you choose Amount as your statistic. If you prefer, you can remove or add statistical measures from your statistics. Your QlikView Financial Analysis of Cheyenne Company should appear similar to the following screenshot:

Figure 1-2: Our financial analysis QlikView application

To load more than one spreadsheet or to load from a different data source, we must edit the load script. The Edit Script dialog is opened from the File menu or by clicking on the Edit Script symbol in the toolbar.

Figure 1-4: A closeup of the File menu showing Edit Script

From the Edit Script interface, we can modify and execute a script that connects the QlikView document to an ODBC data source (or to data files of different types) and also pulls in the data source information.

Our first script was generated automatically; but scripts can be typed manually, or automatically generated scripts can be modified. Complex script statements must, at least partially, be entered manually. The statements, expressions, functions, and so on available for the creation of scripts are described in the QlikView Help: Script Syntax and Script Expressions.

The Edit Script dialog uses autocomplete so, when you type, the program tries to predict what is required in the script without you having to type it completely. The predictions include words that are part of the script syntax. The script is also color-coded by syntax components. The Edit Script interface and behavior can be customized by choosing Tools and Editor Preferences.

At the top of the dialog, a menu bar, with various script-related commands, is found. The most frequently used commands also appear in the toolbar. In the toolbar, there is also a drop-down list for the tabs of the Edit Script wizard.

Once we have clicked on the Edit Script menu item, we will see the following script in the script interface:

Figure 1-5: The Edit Script interface with our generated script

The script in the Edit Script interface is the automatically generated one that was created by the wizard when we started this QlikView file. The automatically generated script picked up the column names from the Excel file and put in some default formatting scripting. The actual text of the script is as follows:

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';

LOAD Company, 
     [AcctGroup], 
     [Account-Name], 
     Count, 
     Unit., 
     Acct.5, 
     Acct.6, 
     Nature, 
     CostCenter, 
     Month, 
     Year, 
     Amount
FROM
[C:\Users\User\Documents\My Docs\Packt Publishing\QlikView Finance Book\QVFA Chapter 1\CHData-OCT.xlsx]
(ooxml, embedded labels);

We can change our date formats, month names, day names, our thousands, and our decimal separators by changing them in the automatically generated formats. We can copy this part of the script into new blank scripts to get started. The language selection that we made during the initial installation of QlikView determines the defaults assigned to this portion of the script.

We can add data from multiple sources, such as ODBC links, additional Excel tables, sources from the Web, FTP, and even other QlikView files.

Our first Excel file, which we used to create the initial QlikView document, is already in our script. It happened to be October 2013 data, but what if we wanted to add November data to our analysis? We would just go into Edit Script from the File menu and then click on the script itself.

After navigating to the script location where you want to add your new code, click on the Table Files button below the script and toward the center-right—the first button in the column. Click on NEXT through the next four screens unless you need to add column labels or transform your data for analysis in some way. The following section is added to our script:

LOAD Company,
     [AcctGroup],
     [Account-Name],
     Count,
     Unit.,
     Acct.5,
     Acct.6,
     Nature,
     CostCenter,
     Month,
     Year,
     Amount
FROM
[C:\Users\User\Documents\My Docs\Packt Publishing\QlikView Finance Book\QVFA Chapter 1\CHData-NOV.xlsx](ooxml, embedded labels, table is LNData);

Comments can be added to scripts using // for a single line or by surrounding the comment by a beginning /* and an ending */; they show up in green. After using the OK button to get out of the Edit Script interface, there is another File menu item that can be used to see whether QlikView has correctly interpreted the joins. This is the Table Viewer menu item. You cannot edit in the Table view, but it is convenient to visualize how the table fields are interacting.

Tell the Edit Script interface OK by clicking on the OK button in the lower-right corner. This will save the changes to the script. Now, using the File menu, navigate below Edit Script to the RELOAD menu item, and click on that to reload your data. If you receive any error messages, the solutions can be researched in the QlikView Help. In this case, QlikView knew that we were adding data to the same table, the layout was the same, and the column names were the same. But looking at company List Box and Amount Statistics Box, we see everything added together. The following screenshot shows this:

Figure 1-6: Data doubled after the reload with the additional file

The reason why this is happening is that we do not have a selection to split the months and only select October or November or to split October from November. What do we do? Now that we have more than one month of data, we can add another List Box with Months. This will automatically link up with our Chart and Straight Table sheet objects to separate our monthly data.

First, we will add a new List Box for Months. Right-click on the sheet and select New Sheet Object | List Box. In the General tab, we will choose Field (labeled Month in the Excel files). Then, we go to the Sort tab and check Sort by Text. Finally, we choose the icons that we want to appear on the top bar of List Box in the Captions tab.

When we choose OCTOBER or NOVEMBER, our sheet objects automatically show the correct sum of the individual months. Your QlikView document will now look somewhat similar to this next screenshot, depending on how you arranged your added sheet objects:

Figure 1-7: A QlikView document