P&L pivot chart extension

You can use the P&L pivot chart extension to create a pivot table with a layout for profit and loss reporting. You can color cells according to performance and style the chart with custom fonts and alternating row colors. You can also export the chart to Excel including formatting. The P&L pivot chart is included in the Visualization bundle.

P&L pivot chart dimensions and measures
Dimensions Measures Result
1 dimension up to 9 measures A table with one row for each dimension value and one column for each measure.
2 dimensions up to 8 measures A pivot table with one row for each value of the first dimension and one column for each measure pivoted using the second dimension.

Coloring cells to show performance

You can color cells to show performance according to a scale of Poor, Fair and Good. All rows and columns are colored by default, but you can choose which columns and rows to color if you want to.

  1. Make sure that Enabled is set to On under Appearance > Color by condition.
  2. Select which rows to color by performance.

    Set Color all rows by condition to Specified rows.

    Add rows to color by name (dimension value) with Add row to color.

  3. Select which measures to color by performance.

    Set Color all measures to Specified measures.

    Add a list of measures by number in Measure indices with the first measure of the chart numbered zero. Separate the measures with a comma.

    Example: Color the first, third, and fifth measure.

    0,2,4

  4. Set performance limits and colors.

    You can set the range limits for Poor and Fair.

    • All cells with a value lower than the Poor range limit are displayed with the background color and text color set for Poor.
    • All cells with a value lower than the Fair range limit, but higher than Poor, are displayed with the background color and text color set for Fair. You should set the Fair range limit higher than Poor.
    • All other cells are displayed with the background color and text color set for Good.

Styling the chart using a style template

You can create a layout for the chart, for example to show a profit and loss report, using a style template.

Note: You need to be able to add and load a CSV file to the app.
  1. Create a style template as a CSV file. Use the style template format described below.
  2. Load the style template to your app as one field. When you add the file, do not use semicolon as field separator, each row should be loaded as one field.
  3. Set Style template field under Appearance > Table format to the name of the template field you added.

You can load several style templates in your app, and change the layout with Style template field.

Style template format

The style template is created as a comma separated text file (CSV) using UTF-8 encoding.

The style template rows need to align to the data in your first dimension. You need to refer to a dimension value in the template. The dimension value should be first in every row. You do not need to specify all ros/dimension values in the template.

Each row in the template should be in the following format. It is not required to use a header row.

DimensionValue;Bold;Background;FontStyle;TextColor;Align;FontSize;Comment
  • DimensionValue

    The dimension value of the row that you want to style.

  • Bold

    Set to <bold> if you want bold text.

  • Background

    Set a background color. You can use <dark>, <night>, <soft>, <red>, <orange>, <violete>, <blue>, <green> or a color code in RGB format, for example rgb(183,219,255). The default background color is white.

  • FontStyle

    You can set the font style to <italic> or <oblique>.

  • TextColor

    You can set the color of the text to <white>. The default background color is black.

  • Align

    You can center align the text with <center>. The default alignment is left for text and right for numeric values.

  • FontSize

    You can set the font size to <large>, <medium> (default) or <small>.

  • Comment

    You can use the <comment> tag to replace all zero values with a space. This is useful when you want to include a sub header row. without values.

You can also use the style tags in any order, and exclude tags that are not used. These rows will give the same result:

Operating expenses;<bold>;;<italic>;;;;
Operating expenses;<italic>;<bold>

Style template example for profit and loss reporting

Cost of goods sold;<bold>;RGB(225,226,226);;;;; Extraordinary items after tax;<bold>;RGB(193,216,47);;;<center>;<large>; Extraordinary items;<bold>;;<italic>;;<center>;;<comment> Financial revenue & expenses;<bold>;;<italic>;;<center>;;<comment> General & administrative expenses;<bold>;;<italic>;;<center>;;<comment> Gross profit;<bold>;RGB(193,216,47);;;<center>;<large>; Income before tax & extraordinary items;<bold>;RGB(193,216,47);;;;<large>; Manufacturing overhead;<bold>;;<italic>;;<center>;;<comment> Net costs of goods sold;<bold>;RGB(225,226,226);;;;; Net gain on sale of land;<bold>;RGB(193,216,47);;;<center>;<large>; Net Income (Profit);<bold>;#191970;;<white>;<center>;<large>; Net mfr overhead;<bold>;RGB(225,226,226);;;;; Net sales revenues;<bold>;RGB(225,226,226);;;;; Operating expenses;<bold>;;<italic>;;;; Operating income before taxes;<bold>;RGB(193,216,47);;;;<large>; Other general & admin expenses;<bold>;rgb(128, 191, 255);<white>;;<center>;<large>; Revenues;<bold>;;<italic>;;<center>;;<comment> total general & admin expenses;<bold>;#efefef;;;;; total operating expenses;<bold>;rgb(128, 191, 255);<white>;;;; Total selling expenses;<bold>;RGB(225,226,226);;;;;

To use this template, you need a data file where the first dimension contains values that correspond to the first item of each row, for example Cost of goods sold.

P&L pivot chart styled with the layout template in the example

Exporting the chart to Microsoft Excel

You can export the chart to Microsoft Excel including formatting by clicking the XLS icon in the rightmost cell of the chart header.

Warning: When you open the exported chart in Microsoft Excel, you may get a warning message that the file format and extension does not match. You should still be able to open the file by clicking Yes.

Limitations

For information about general limitations, see Limitations of extension bundles supplied by Qlik.

  • There is a limitation for data transfer between Qlik Engine and an extension. This limits each request for data to 10000 elements, for example, 1000 rows and 10 columns. P&L pivot can make automatic requests for more data using pagination.

    You can set the limit of how much data to request with Pagination > Max pagination loops. The default value is 20000 elements (20k cells) and the maximum value is 100000 elements.

    You can also modify the error message displayed to the user when the data limit is exceeded. The recommended workaround when the data limit is exceeded is to apply filters to the data to limit the data transfer.

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?