In the Financial_Perspective_Sandbox.qvw application that is found in the C:QlikSourceData1002.Finance_Perspective1.Application, let's start by creating a straight table with the following properties:

='' 

only({1<[Account - Report]
={'Income_Statement'}>} 
[Account - Order]) 

=monthname(
max(
[AsOf Year-Month]
)) 
& 
chr(10) 
& 'Monthly'

sum({$<[Account - Report]={'Income_Statement'},[Month]=,[Year]=,_MonthSerial={'$(=max(_AsOfMonthSerial))'}>} [GJ Amount]) * only([Account - Factor])

%

sum({$<[Account - Report]={'Income_Statement'},[Month]=,[Year]=,_MonthSerial={'$(=max(_AsOfMonthSerial))'}>} [GJ Amount]) * only([Account - Factor])/sum({$<[Account - Report]={'Income_Statement'},[Account - Concept]={'Total  Revenue'},[Month]=,[Year]=,_MonthSerial={'$(=max(_AsOfMonthSerial))'}>} Total [GJ Amount]) * -1

=chr(10) & 'YTD'

sum({$<[Account - Report]={'Income_Statement'},[Month]=,[Year]={$(=max(AsOfYear))},_MonthSerial={'<=$(=max(_AsOfMonthSerial))'}>} [GJ Amount]) * only([Account - Factor])

%

sum({$<[Account - Report]={'Income_Statement'},[Month]=,[Year]={$(=max(AsOfYear))},_MonthSerial={'<=$(=max(_AsOfMonthSerial))'}>} [GJ Amount])* only([Account - Factor])/sum({$<[Account - Report]={'Income_Statement'},[Account - Concept]={'Total  Revenue'},[Month]=,[Year]={$(=max(AsOfYear))},_MonthSerial={'<=$(=max(_AsOfMonthSerial))'}>} Total [GJ Amount])* -1 

The first expression looks unusual. It doesn't aggregate anything and doesn't even appear in the example income statement. That's because it works as a placeholder for account groups that do not have any GJ entries during the selected period. Unlike QlikView, legacy reports usually show dimensions even when the sum of their corresponding metric is zero. We change the expression's Text Color to white() so that it is hidden from the user.

Now that we've added the necessary dimension and expressions let's change a few detailed properties and apply the financial report metadata to the QlikView object:

It is good practice to align the column label in the same way that we did to its data. We also keep the label close to the data and vertically align the label on the bottom of a two-line header. Make sure to set these alignments for every expression.

Along with proper formatting, we want to alert users to any negative values. Such values in one of the income statement's calculated groups, such as Gross Profit or Operating Profit, indicate a loss. If found in other groups they may indicate an unusual transaction that affects an account contrary to its normal balance. We enable these alerts in the Visual Cues tab.

If we have room enough to only use whitespace to divide columns, let's remove the borders in the Style tab in two easy steps:

Finally, let's perform these last two steps to clean the number format and to hide the caption:

We should now have a fairly clean income statement, but what if we want to go a little further and change the background of the column header or row borders? We can use a hidden feature called Custom Format Cell to make these additional changes.

Straight tables and pivot tables have an additional properties dialog to further customize a table's style. It is not available by default, so first we go to the Settings file menu, and then User Preference….

In the Design tab of User Preferences, tick the option to Always Show Design Menu Items, as shown in the following screenshot:

We now have a new option called Custom Format Cell when we right-click over any table:

This option opens a window that allows us to define the cell borders, cell backgrounds, text color, text style, and text size of each dimension and expression. Any change that we make to one cell applies to all other cells belonging to the same expression or dimension. In other words, we cannot define a different format for two different cells of the same expression or dimension.

Regardless of this limitation, Custom Format Cell does provide us with several options to create a custom table style. Let's go ahead and make our final changes to the format of the income statement as follows:

You will notice that on the left-hand side of the window, we can navigate throughout the table and define the style for each dimension and expression without having to close the window and reopen it by right-clicking on a different area of the table. Let's start by clicking on the first expression's column header:

If we add a few more metrics and move the account names to the center of the table, we can achieve a more detailed winged report with monthly metrics on one side and year-to-date metrics on the other. You can review the following example in the exercise solution file:

Now that we have a well-formatted income statement, let's examine how we can use common visualization techniques to make it more effective. We use a slightly modified version of the previous user story to identify the key points that executives look for in an income statement.

Modern accounting has been around for more than 500 years, and we are probably not going to change how accountants visualize data in our lifetime. The accountant's instinct to use numbers and tables to solve this user story may result in something like the following example, which is a common format to analyze how an income statement is trending:

Any argument to say that they shouldn't analyze data in this way will cause them to question QlikView's ability to satisfy their reporting needs. Therefore, I recommend that we do it in the way that they are most comfortable with. Luckily, the AsOfCalendar table makes this report possible without reverting to methods, such as island tables and if-statements, that can cause the report's calculation time to grow exponentially. You can review the details on how to make the table in the exercise solution file.

Then, in addition to the table, we should propose more abstract ways to view the data more efficiently. Converting a table full of metrics into an abstract visualization is one of the most difficult challenges that we will ever face as data visualization designers. We have to come to terms with the fact that we cannot fit every metric into one chart without making it as hard to read as the originating table. Regardless of whether we use lines, bars, points, or some purportedly omniscient chart, we cannot fit everything into one visualization.

The best solution is to create a group of charts in which each element highlights a different aspect of the income statement. For example, we can create one bar chart to analyze year-to-date amounts and variations, and another graph to analyze monthly amounts and variations. Then we can add a line chart to view the trend of the most important account groups, and another to view the trend of detailed expense accounts.

Another alternative is to use the same familiar table structure to create a grid chart. Again, if we try to fit everything into one chart, we have to sacrifice a certain level of detail, metrics, or dimensions. At the same time, we can use the following grid chart to start a story that will lead us to look at specific charts and tables as we dive deeper into our story:

In order to make this chart, we have to sacrifice measuring year-to-date metrics. We've maintained the same number of dimensions, but we've replaced actual numbers with color and year-over-year variation with an arrow. Even so, we can quickly perceive that we had our highest sales in July and August 2015, while strangely, our cost of goods sold was highest in December 2014. The fact that cost of goods sold is not always correlated to sales is curious. Such an observation may be a great place for a business user to start a story that leads to price and inventory analysis.

In the Financial_Perspective_Sandbox.qvw application, let's first create a variable that makes the chart expressions cleaner, as follows:

We now have an income statement grid chart. We can experiment with the options that we learned earlier in this section to add cell borders and any fine-tuning adjustments. After doing so, let's move on to the next important financial report—the balance sheet.