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:

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:

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:

Figure 2-4: Here, the Back and Forward buttons are enabled

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:

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:

Figure 2-6: The KPIs tab with completed KPI gauges

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.

Figure 2-7: Examples to examine the documents that came with the installation