Re: Qlik P & L Reporting - Qlik Community

GrantBooth · ‎2021-10-13

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.

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?

Lech_Miszkiewicz · ‎2021-10-13

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 · ‎2021-10-13

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 · ‎2021-10-13

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 · ‎2021-10-13

by the way - resizing columns is here:

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 · ‎2021-10-13

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 · ‎2021-10-13

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

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.