Skip to main content

Design

The Design blog is all about product and Qlik solutions, such as scripting, data modeling, visual design, extensions, best practices, and more!

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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

26 Comments
j-rsr
Contributor
Contributor

Hi Jennel,

Is it possible to share a sample QVD? I'm struggling setting up the datamodel to get the calculated rows like 'Nett Sales', 'Total Cost of Sales', etc. Is it also possible to ungroup the categories to see the underlying ledgers (like in a regular pivot)?

 

Regards,

J-R

0 Likes
2,081 Views
fishmanl
Contributor II
Contributor II

hi

 

after exporting the PNL report to Excel, all the excel functionality are lost - for example Summing the numbers.

i noticed that the export is in HTML format and even after saving it to XLS it still refuse to be used as excel files

any solutions?  

0 Likes
1,961 Views
mg_gsi_da
Contributor III
Contributor III

Did someone found out how to add a row for totals on the bottom or top as the standard Pivot table does?

0 Likes
1,736 Views
robert99
Specialist III
Specialist III

Hi @mg_gsi_da 

I couldn't find a way to do it (but this was when this was first released)

So instead use the std pivot table (when I want a total) and remove the headings when required (so the PT becomes a report not a PT). This will change all the pivot tables for one sheet only

[tid="meta.rows"],

[tid="meta.columns"]
{display:none;}

https://www.youtube.com/watch?v=9lhL3Nrel5Q

1,715 Views
mg_gsi_da
Contributor III
Contributor III

Hi @robert99 

thx for the link. Great stuff.

 

1,695 Views
Neil_Southern
Luminary Alumni
Luminary Alumni

This is good however you cannot remove the scroll regardless of size of object or data,  if anyone knows how this can be removed please post answer

1,291 Views
rarpecalibrate
Contributor III
Contributor III

@Neil_Southern ,

You can either add the following css to your theme.css file when using the Theme API or add it to the Multi KPI extension in the Viz bundle (Appearance -> Styles):

.qv-object-qlik-smart-pivot .data-table .row-wrapper {
  overflow: auto !important;
}

Regards,

Ryan Arpe

0 Likes
1,248 Views
fatinfazrizanor
Partner - Contributor III
Partner - Contributor III

Hi,

How do i change the width size of 1st dimension? Do i need to change from the js file? and if yes, do you know which line i have to change?

Thanks.

722 Views
doespirito
Creator
Creator

Hi Jennell,

Nice extension but which does not replace the intervalmatch method on Qlikview that you explained to us. The data must already be aggregated in the data model to use P&L Pivot Chart extension on Qlik Sense. Data never comes out of an ERP in this form.
Shame.

143 Views
JordyWegman
Partner - Master
Partner - Master

Hi @doespirito ,

If you don't want it being aggregated, check out the Finance Extension from Vizlib. I use this one often at customers to show their P&Ls, balance sheets etc.

Jordy

Climber

0 Likes
120 Views