Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
GrantBooth
Contributor III
Contributor III

Qlik P & L Reporting

I'm trying to create basic P&L reporting within Qlik, and I'm looking for some recommendations for a potential solution.

Currently I'm using Pivot Tables which work, but they don't let me create custom rows for Profit Before Tax or Capex.

I've had a look at P&L Pivot that is currently packaged with Qlik and it's giving me correct results that look awful - if I could work out how to resize the columns it would go a long way towards solving my problem.

Untitled.png

 

Ideally the solution would require no updates to the data set and would not require that calculations are done prior to the load, what are some good ideas?

 

 

 

 

7 Replies
Lech_Miszkiewicz

I would suggest studying features and options available in help document here: 

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

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
GrantBooth
Contributor III
Contributor III
Author

Thanks for that response, it was something I was aware of prior to posting but I feel that it is really light on detail. I could certainly follow that for styling, and it is something that I'm doing,  but it does not talk about creating custom rows, which is the most important part of creating a P&L. Are you able to help out with that side of things?

Lech_Miszkiewicz

Hi @GrantBooth 

What do you mean by "custom rows"? Are you referring to certain groupings which have to be created in Qlik Sense data model prior to building visualisations? like :

  • Sales
  • Cost
  • GrossProffit
  • EBIT
  • EBITA 

if that is the case all principles are described very well here: https://community.qlik.com/t5/App-Development/QlikSense-Profit-and-Loss-Statement-without-an-extensi...

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Lech_Miszkiewicz

by the way - resizing columns is here:

Lech_Miszkiewicz_0-1634174855998.png

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
GrantBooth
Contributor III
Contributor III
Author

Yes, that is exactly what I'm trying to do. If you're very familiar with a particular tool it might seem obvious, but as a relatively new user I'm baffled by the  number of steps to do such a very simple thing such as EBIT.  Is there really no other way to create rows such as EBIT/EBITA outside of those steps in the link you posted?

Lech_Miszkiewicz

Well maybe that link is little bit too complex, but at the end P&L solution is based on account groupings. Lets assume you have 2 tables in your Qlik Sense (GLLedger and Acc grouping tables). To explain whole concept we go for below GL Ledgertable which needs to have at least below columns:

  • Account
  • Date/Period
  • Value

Grouping table  which needs to have groupings for all your "Custom rows"

  • Account
  • Grouping

Lech_Miszkiewicz_0-1634176281187.png

So once you load both tables to Qlik Sense and you use Grouping as your dimension then you write measure sum(Value) that automaticaly creates Sales, Cost and Profit rows (or as you call it custom rows) and calculates results based on pure sum() of debits and credits.

So in principle this exercise is actually very simple. 

The work which needs to be done is to create grouping table and that possibly can be tricky if you are new to Qlik. This can be as simple as in my example or more complex - based on account ranges and intervals.

More links and examples here:

https://community.qlik.com/t5/QlikView-Documents/How-to-Create-a-Profit-and-Loss-Statement-in-QlikVi...

 

There are other possibilities, but in my opinion they are more difficult to build, maintain, explain and perform a lot worse than example I have given. 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
AlbertoV
Partner
Partner

The key is the style template. I worked on a P&L related app a few months ago and after fiddling with the Styling template I was able to deliver a nice looking one. 

I hope these help:

Help: Styling the chart using a style template 

YouTube: P&L Pivot - Qlik Sense