In our previously loaded sample data from Chapter 1, Getting That Financial Data into QlikView, sales is a grouping in the AcctGroup column and is a credit. It comes directly from the source system as a negative so that, when everything is summed up, we get a trial balance. That is why we could see the out of balances in our first tab. Sales will have to be multiplied by minus one (-1) in order for it to appear as a positive number. Also, we would like to have a target number to compare with Sales.
There are two additional sample data Excel spreadsheets available for download from your account at http://www.PacktPub.com that are used in this chapter. One is named Target.xls
and the other is named IS3yr.xls
.
Our target is another Excel workbook named Target.xls
that is available for download, or you can create your own workbook to load. The one available for download has this data:
Company |
Target |
---|---|
Cheyenne Manufacturing |
140000000 |
American Distribution |
0 |
Cheyenne Holding |
0 |
Sales Corp |
1100000000 |
Wheatland |
0 |
Pioneer Payroll |
0 |
Provo |
100000000 |
Cheyenne Co L.P. |
450000000 |
Warranty Care Corporation |
250000 |
Cheyenne National Inc |
0 |
Eliminations |
-908999539 |
To load Target.xls
, choose Edit Script from the File menu. After navigating to the bottom of the existing script location where you want to add your new code, click on the Table Files button below the script and toward the center right; it is 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. If you don't remember what this looks like or how to navigate, refer to Chapter 1, Getting That Financial Data into QlikView, for pictures.
Once we use Edit on the script to load the Target
data, our script code looks similar to this:
LOAD Company, Target FROM [C:UsersUserDocumentsMy DocsPackt PublishingQlikView Finance BookQVFA Chapter2Target.xlsx] (ooxml, embedded labels, table is Target);
Save your changes to the Edit Script interface by clicking on the OK button in the bottom-right corner. Now, using File menu, navigate below Edit Script to the RELOAD menu item, and click on that to reload your data.
Now, we will add a tab to our QlikView document by going to the Layout menu item and selecting Add Sheet. Rename this sheet to Sales by right-clicking on the sheet and choosing Properties from the menu.
Add the following sheet objects:
Now, we will add a Sales thermometer-type gauge to our Sales tab.
Right-click on the Sales tab sheet, and choose New Sheet Object, as you did for Multi Box.
This time, choose Charts, and Chart Wizard will pop up. There are basically two types of gauges: the rectangular type, which are called thermometers because they tend to display data ratios in a horizontal or vertical format, and the ones called radio dial gauges because they display data in a circular format, such as the image on the QlikView Gauge Chart button.
These are the Chart Wizard tab steps related to making the thermometer-type gauge that we want to display for Sales:
=(sum({$<AcctGroup={Sales}>} Amount) *(-1))
Later in this chapter, the expression formulation will be explained.
=(sum(Target))
Sum(Target)
expression using <Ctrl> C./
) and pasting the formula you copied using <Ctrl > V so that the formula now looks as shown here:=(sum({$<AcctGroup={Sales}>} Amount) *(-1)) /(sum(Target))
=(sum(Target))
, because we only created it to copy and paste into our first expression and to practice with the interface.Because we have divided Sales by Target, we get a variance ratio. Use Multi Box to select Company with Sales over a month, and watch the indicator move and the ratio change.
Change your expression to the next formula to get the true variance percentage:
=((sum({$<AcctGroup={Sales}>} Amount) -(sum(Target)) ) *(-1)) /(sum(Target))
Unfortunately, the formatting in this edition of gauge-type charts does not pick up percent formatting.
The three most common financial KPIs used by financial analysts to evaluate a company's performance are Return on Assets (ROA), Return on Investment (ROI), and Return on Equity (ROE). They use these simple ratios because ratios enable them to compare small companies and large companies on an equal footing to determine whether to recommend buying stock in the company, loaning money to the company, or investing in the company in some other manner.
The standard calculation for these ratios is as follows:
In order to create these ratios (ROA, ROI, and ROE) in QlikView, we need to use set analysis expressions. The basic format of a set analysis expression looks similar to this:
sum([{set_expression}][ and other related expressions ])
The set expression is usually aggregated and can be modified by commands, such as [distinct]
, [total]
, specifying fields, or other expressions. Aggregation functions, such as Sum
or Average
, by default, aggregate over the current selection in your QlikView document, so the other options shown can be used to modify that behavior. For our ratios, we want to define other sets of field values and use them in our charts instead of the current selection. In our first KPI dashboard, we need to show groupings of accounts based on the Acct_Group or Nature data columns to get the correct calculations.
Set expressions always begin and end with curly brackets. For example, sum({1} Amount)
, where {1}
is a set expression represented using the shortcut expression {1}
.
Under normal circumstances, you could build one chart with the dimension Nature and the measure sum (Amount). User selections of Company and AcctGroup would then determine what is shown. However, here, we want our chart or gauge to always show the same ratio and to only be affected by date selections and individual company selections. An initial, but longwinded, set expression for ROA for our sample data would look like the following, but, as we continue, we will learn how to shorten these statements into set expressions that QlikView will understand:
set_expression= { all of Amount< for Nature IS >}/{all of Amount < for Nature Assets>} set_expression= { current selection of Amount< for Nature IS >}/{current selection of Amount < for Nature Assets>}
Here, Nature IS
and Nature Assets
are values originally from a column of information named Nature that we loaded to QlikView.
To state an initial set expression for ROI for our sample dataset, the set expression would look similar to this:
set_expression= { all of Amount< for Nature IS >}/({all of Amount < for Nature LT_Liabilities >} + {all of Amount < for Nature Equity >}) set_expression= { current selection of Amount< for Nature IS >}/({current selection of Amount < for Nature LT_Liabilities>} + {current selection of Amount < for Nature Equity>})
To state an initial set expression for ROE for our sample dataset, the set expression would be similar to this:
set_expression= { all of Amount< for Nature IS >}/{all of Amount < for Nature Equity >} set_expression= { current selection of Amount< for Nature IS >} / {current selection of Amount < for Nature Equity>}
But we can make our expression simpler using expression shortcuts. Expression shortcuts that we can use are as follows:
all
is 1.
$
And the syntax for modifiers in this case is as follows:
set_modifier = <field_name={field_value,[field_value]}
Using the shortcuts, we get the KPI measures for our charts, which use set expressions:
=sum({1<Nature={IS}>} Amount) / sum({1<Nature={Assets}>} Amount) =sum({$<Nature={IS}>} Amount)/ sum({$<Nature={Assets}>} Amount)
Our Sales are credits and our Assets are credits (negative numbers) as they come from the ledger source system in order for our trial balance in the previous chapter to work. We need to make one more change to our formula to compensate for our net income being negative when it is more than our expenses. We multiply each formula line by minus one. So, here's how our expression now looks:
=(sum({1<Nature={IS}>} Amount) / sum({1<Nature={Assets}>} Amount)) * (-1) =(sum({$<Nature={IS}>} Amount)/ sum({$<Nature={Assets}>} Amount)) * (-1)
Expression shortcuts known as Set Identifiers define the relationship between the set expression and field values (columns) or the expression that is being evaluated. Set identifiers can be combined using set operators, which follow the rules of mathematical operators.
In addition to the two expression shortcuts that we used earlier, there are a few other shortcuts that we might want to use. The following are navigational expressions that allow us to use previous calculations for forward calculations, comparisons, and trending-type operations:
$N
where N
is a number representing the previous evaluation expression to use. For example, $1
means use the previous selections, and $0
means use the current selections.$_N
where N
is a number representing the number of forward steps in an evaluation. For example, $_1
means the next step in the selections.These only work if you have made backward and forward navigational steps within a QlikView sheet object. Once you have made selections and changed them, the Back button and the Forward button become available to flip back and forth through the prior selection:
There are several set operators that look similar to what we think of as mathematical operators that can be used in set expressions. Their meaning and function is determined by where they are used in the set expression. All the set operators use sets as the operands and return a set as the result. Within a group, the set expression is evaluated from left to right just as a mathematical expression would be evaluated. So, it is necessary to group expressions using standard parentheses ()
to indicate how an expression should be evaluated. For example, X + (Y - Z)
is not the same as (X + Y) - Z
and is also different than (X-Z) + Y
where X
, Y
and Z
are sets rather than individual numbers. This makes it even more important to designate the evaluation order with parentheses in set expressions because we are actually working with groups of numbers instead of simple math.
For example, sum( {1-$<AcctGroup={Sales}>})
returns the sales for everything excluded by the current selection because of the Exclusion Set Operator between the 1
and the $
.
Now, we will add the ROA-, ROI-, and ROE-type gauges to the KPIs tab. Prepare by copying our Sales tab and pasting it as a new sheet. Rename the sheet KPIs. You can delete your Sales vs Target object if you want, or experiment with minimizing it.
Next, we will make our three returns KPIs. Choose Charts, and Chart Wizard will pop up. These are the steps to making the thermometer-type gauge that we want to display for each measure:
Return on Assets
, and select the Gauge Chart type by clicking on the button.=(sum({$<Nature={IS}>} Amount) / sum({$<Nature={Assets}>} Amount)) * (-1)
=(sum({1<Nature={IS}>} Amount)/ sum({1<Nature={Assets}>} Amount)) * (-1)
Having the two expressions enables us to use the Back button in QlikView.
Repeat the preceding steps for Return on Investment
using these two formulas:
=(sum({$<Nature={IS}>} Amount) / (sum({$<Nature={Equity}>} Amount) + sum({$<Nature={LT_Liabilities}>} Amount)) ) =(sum({1<Nature={IS}>} Amount) / (sum({1<Nature={Equity}>} Amount) + sum({1<Nature={LT_Liabilities}>} Amount)) )
Size your boxes with the KPIs to approximately the same size, and then repeat the steps for Return on Equity using these two formulas:
=(sum({$<Nature={IS}>} Amount) / (sum({$<Nature={Equity}>} Amount)) ) * (-1) =(sum({1<Nature={IS}>} Amount) / (sum({1<Nature={Equity}>} Amount)) ) * (-1)
Size your boxes with the KPIs to approximately the same size and arrange them in order on the sheet. Your KPIs tab should look somewhat similar to the next screenshot:
Use the Multi Box or List Box to change your KPIs and watch the numbers and levels change.
What happens if you reverse the set operation formulas by reversing their order and promoting one over the other so that the one using $
is first?
=(sum({$<Nature={IS}>} Amount)/ sum({$<Nature={Assets}>} Amount)) * (-1) =(sum({1<Nature={IS}>} Amount) / sum({1<Nature={Assets}>} Amount)) * (-1)
The KPIs are frozen at the All-level selection because we have moved that expression to the top of the two expressions for each KPI display.
For additional information, see the help feature of QlikView. You can also examine the various QlikView applications that can be downloaded when you install QlikView. You will find them in the Install
folder. Data Visualization
, in particular, has examples of all the chart display types. We will investigate more of these in the following chapters.