Finance Report with Waterfall Chart - Qlik Community

Jennell_Yorkman

Several years ago, I wrote a blog post on how to create a profit and loss statement in QlikView. @Patric_Nordstrom has built upon this method and built a financial statement in Qlik Analytics with a straight table and waterfall chart using inline SVG. In this blog post, I will review how he did it.

Here is an example of the financial statement structure.

There are plain rows, such as gross sales and sales return where the amount is the sum of the transactions made against the accounts. There are subtotals such as net sales and gross margin which are a sum of the previous plain rows. And there are also partial sums such as total cost of sales that is a sum of a subset of the previous plain rows, but not all the previous plain rows.

Patric identified two functions, RangeSum() and Above() that are suitable for calculating the subtotal and partial sums in a table. The RangeSum function sums a range of values, and the Above function evaluates an expression at a row above the current row within a column segment in a table. The above function can be used with 2 optional parameters – offset and count to further identify the rows to be used in the expression.

The layout table below is used as a template for the financial statement.

The RowStyle column is used for styling the rows making text bold or underlining it. Currently, we cannot use tab or blank tags but hopefully in the future this will become available in the straight table.
The RowTitle is the account category that is to be used in the financial statement.
RowType is used to as input to calculate the offset and count for the above function.
AC is the actual amount.

The AC column is included here in the layout file for demo purposes but could be calculated from the accounts and transactions in the data model as well.

In the script, the layout table was loaded, and additional fields were created to support the waterfall chart, specifically offset and count fields to be used with the above function.

Here is a view of the layout table with the new fields that were created in the script.

complete layout table.png

After the layout table is loaded and the new fields are created, some master measures can be created to be used in the inline SVG expression. Here are the 3 master measures Patric created:

mBar is the bar length with an offset that is always 0.

mStart is the starting position of the bar in the waterfall chart and for subtotals, this is always 0.

mMax is the max bar length which is used to scale the bars in the waterfall chart.

Now the straight table can be created. The RowNr field is added for sorting purposes. The RowTitle field and the AC fields are added to show the account groupings in the financial statement along with their value. The below inline SVG expression for the waterfall chart is the last column added to the straight table. It is made up of 3 parts:

A plain line with an offset
A thin gray line where x=0
A label

inline svg.png

The if statement on line 1 will determine if a bar is displayed. Bars will only be visible if the RowStyle is not blank.
Line 2 has the viewBox settings and sets the 0 for the x-axis.
On line 3 is the light gray line where x=0 and it is displayed on all non-blank rows of the financial statement.
Lines 4 and 5 in the yellow box is the plain line with the offset, scaled using the mMax measure to control the length of the line.
Line 6 handles the bar color, light green for positive values and red for negative values.
On line 7, the if statement is used to set the stroke width of the bar. A thin line is used if the RowType is retrosum and a wider line is used for all other bars.
In the red box, on lines 10 through 13, the label text is set and placed either to the left or right of the bar depending on the value. Positive values are placed to the right of the bar and negative values are placed to the left of the bar.

The result of the financial statement looks like this:

To add the text styling (bold and underline) from the layout table, the RowStyle field was added to the text style expression in the RowTitle and AC columns.

Indentation is added by using the repeat function in the RowTitle column. It will repeat a non-breaking space 6 times if there is a tab tag in the RowStyle field. Otherwise, no indentation is done.

If the RowStyle is not blank, a bar is displayed for the waterfall chart and the sum value for the actual amount (or mBar) in this case is displayed.

The chart column representation is set to Image in the properties of the straight table.

While this method looks complex, it is a simple and clean solution for adding a waterfall chart to a financial statement using straight table features and inline SVG. Using the layout table and inline SVG provides room for customization so that the financial report meets the needs and requirements of the user or customer.

Thanks,

Jennell