Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
Jennell_McIntire
Employee
Employee

Several years ago, I blogged about how I used IntervalMatch to create a profit and loss statement. Now that Qlik Sense has the P&L Pivot chart extension in the visualization bundle, I decided to test out the extension to see how it may help create a P&L report in Qlik Sense. This blog will review some of my findings and what was involved to create the P&L Pivot chart shown below.

 

chart.png

 

The P&L Pivot chart extension provides a lot of properties that allow you to change the style and coloring in the chart. You can find a complete list of the properties here in Qlik Help. The chart above uses one dimension and 6 measures but I could have used up to 9 measures with my single dimension or used 2 dimensions and up to 8 measures. After loading the data into Qlik Sense, my next step was to create a style template that I could use to handle the formatting of the chart.  Here is a snapshot of the template in Excel:

 

template.png

 

I added a header row so that I could easily see what options I could set but it is not required and can be omitted. In the styling template, Column A should include the data that is in the first dimension of the chart you will be applying the template to. Note that this data is case sensitive.  The other columns have the various styles that can be modified via the template.

 

Here is an example of the same template in a CSV file.

 

csv.png

 

If a template is used, it needs to be loaded via the script. Here is how you can load the Excel and CSV versions of the templates:

 

Excel:

 

excel script.png

 

CSV:

 

csv script.png

 

The key in both scripts is that the entire template needs to be loaded into one field which can later be selected from the Style template field drop down in the Properties window of the P&L Pivot chart. In the Excel script, the styles are concatenated into one field separated by semi-colons. In the CSV file, the file format is set to Fixed record to load all the data into one field. Note that the name that you give the field in the script will be the template name you select from the Style template field drop down.

 

Each row of the template should have this format:

 

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

 

  • DimensionValue is the data value of the dimension in the row that you would like the change
  • Bold is used to bold the text in a row
  • Background is used to set the background color of a row
  • FontStyle is used to change the font style of a row to italic or oblique
  • TextColor is used to change the default black font color of a row to white
  • Align is used to set the alignment to center
  • FontSize is used to change the font size of a row
  • Comment is used to replace all zeros with a space

 

Check Qlik Help to see the default and styling options that are available in the styling template.

 

There are many additional properties that can be set in the properties window of the P&L Pivot chart extension. I kept many of the defaults but here are some that I changed. In the Table Format section of the Properties, I checked Indent to indent the P&L Heading. The columns can be narrow so I moved the Column width slider all the way to the right, (for max width), and changed the Font family so that I could see as much of the numeric values in the chart as possible. I tweaked the header coloring in the Header format section. I opted not to color variances below 0% red because then I would lose some of my styling template changes. While I used a styling template in my example, you do not have to. The P&L Pivot chart extension can be styled via the Properties window without using a template.

 

The P&L Pivot chart extension provides a ton of styling options that can be used to easily make your profit and loss statement looks exactly the way you want. It is worth checking out or you can watch this video to learn more.

 

Thanks,

Jennell

27 Comments
doespirito
Creator
Creator
Hi,
Thank you for your message.
Can you give me a price range for this extension?
thank you.
Qliksense server on premise,
Arnault
644 Views
Jennell_McIntire
Employee
Employee

Hi Arnault,

There is now a P&L Pivot chart available in Qlik Sense. 

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Visualizations/Visualizat...

Best,

Jennell

0 Likes
623 Views
doespirito
Creator
Creator

Hi Jennell,

thank you for your message.
I'm having trouble understanding the usefulness of P&L Pivot chart since you can't add row totals. The only solution to have something useful and presentable is to go through the template file, which is quite complicated.
And it is still necessary to have data already aggregated in the data model, which is yet another difficulty.
After having tried in all directions this extension I remain very frustrated.
thank you

Arnault

0 Likes
607 Views
JordyWegman
Partner - Master
Partner - Master

Hi @doespirito ,

Please send me a DM for this information.

Jordy

Climber

0 Likes
593 Views
marcus_sommer

The P&L views within Qlik aren't aimed to serve legal purposes - and even if there would be a lot of varieties possible between countries, company types and so on. This means it will be always more or less adjustments necessary to get the needed views.

The usefulness of these examples is to show ways of how it could be done but not to provide a 5 minutes efforts solution which could satisfy all requirements. The essential insights should be that the magic comes from a well designed data-model in which all needed dimensionality are created in a kind of a The As-Of Table - Qlik Community - 1466130 against stream-data-measures (crosstable-measures are possible but will increase the UI efforts to pick the wanted ones).

Further it's not necessary to pre-aggregate everything to such a structure else it will also work with millions of records. You may even go further and apply some groupings to switch between multiple companies within a holding and to consolidate them appropriate or to compare older P&L structures against the current ones and many more.

For nothing of them you will mandatory need a special extension else everything could be done with native features but of course such extension will simplify the matter for not beginning by zero.

- Marcus

0 Likes
584 Views
doespirito
Creator
Creator

Thanks for taking time to respond to me.

My initial need is not to make a legal P&L, which would be the simplest because the data is relatively fixed.
But whether in marketing, sales, production or finance we spend our time presenting figures with breaks and subtotals which generally must be highlighted by specific fonts/colors/indentations.


I do not claim to know QS perfectly but it seems to me that it is not possible as standard in QS.


Knowing that you have to go through an aggregation of data and prepare in a template file all the possible occurrences of totals seems to me to be insurmountable when it comes to dynamic data.


And I'm not in favor of buying extensions for something that seems to me to be a basic function.

Best regards

577 Views
nguyen
Partner - Contributor II
Partner - Contributor II

Hi,

How did you define which rows are indented? I found the checkmark in the P&L pivot settings but am not sure how to flag the rows that should be indented.

Best regards, Manh

283 Views