Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
didierodayo
Partner - Creator III
Partner - Creator III

Hi Jennell,  you topic is very clear well done.

Am wondering if you worked out a way to stretch the measure columns? as you can see the text don't show properly.

Thanks

0 Likes
11,979 Views
simonaubert
Partner - Specialist II
Partner - Specialist II

Hi Jennell,

It doesn't work on my Qlik Sense Desktop :image.png

Best regards,

Simon

11,960 Views
grvandriessche
Partner - Contributor II
Partner - Contributor II

I have a question  about sorting.

Obviously your chart is not sorted on the one dimension used.

I tried sorting by expression:

I created a sort field in my script that lools like A0000, A0001

and so on, When I use my sort field as dimension, the table is sorted correctly. When I use a description field as my dimension and the sort field in the sort expression, the table displays a random order.

I think this is a bug.

Without a way to present the dimensions in the desired order, this feature is completely useless. 

0 Likes
11,214 Views
andyyan_explorer
Contributor
Contributor

Hi Jennell,

 

Great stuff you have provided here. Thanks heap!

I just started playing with this new chart. And I came across an issue with the position vertical discrepancy between the header and data row.

Header and Data rows don't align verticallyHeader and Data rows don't align vertically

 

Thought you might be able to share some insights on this. Thanks in advance.

 

Regards,

Andy Y

0 Likes
11,152 Views
didierodayo
Partner - Creator III
Partner - Creator III

Andy,

 

The header issue is resolved in the September 2019 release. Cheers

0 Likes
11,145 Views
robert99
Specialist III
Specialist III

This is a good extension

The changes I would like is

1. Increase 9 or 8 measures to say 15. I have reports that I would like to convert that include 10 and 12 measures (one dimension).

2. Include the option of say <blankmeasures) rather than <comment> on the end. Or change the Qlik instructions. it doesn't just blank out zero entries but all measures. So it did exactly what I wanted but I only stumbled across this by mistake.

3. Include the option of a Total for the Pivot format

But it certainly looked better than using the Table

 

0 Likes
9,308 Views
robert99
Specialist III
Specialist III

I set up a P+L P+L that I thought looked VG. But the users preferred my old P+L using the straight table (see example below)

 

Article3.jpg

6,354 Views
michael_klix
Creator II
Creator II

Thanks for the great evaluation.

 

When I did my first experiments, I came across an old problem and a new on:

1. Column width do not adopt to the content and are not individual per column/measure

2. The P&L extension does not display TEXT. I need a pivot that adds a String as measure (actually a name)

 

Any ideas on that?

 

screenshotscreenshot

0 Likes
6,237 Views
robert_bond
Contributor III
Contributor III

@Jennell_McIntire ,

 

Question: How did you add spaces between line items? I created a measure and then formatted the font as white. 

0 Likes
6,041 Views
anafbatalha
Contributor
Contributor

How to change the formatting of lines in PL Smart Pivot.

For Example: I have a line - Margin Ebitda %. This line has 3 columns. The first the value is ##%, the second the value is ##% and third is p.p (Percentage points).

Ps. I tried to insert in the expression, but doesn't accept/ignore.

 

 

HELP-ME

0 Likes
5,453 Views