Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

juanjulian_hs
New Contributor II

Adding manual rows in a P&L table in Qlik Sense

Hi everyone,

This is my first post in the community and I am sure it won't be last. I was wondering if any of you knew how to add rows within a dimension in order to create a P&L. So far I can pull the data in the following format:

 

Categorised (CTD)
Revenue
CoS - Circuits
CoS - Other
CoS - Resources
Direct Opex
Opex Resources
Overheads
LRAIC Opex
Depreciation
Capex
LRAIC Capex
Net Working Capital

Cost excl OH

However, I need it to end up looking like this?

   

Revenue
CoS - Circuits
CoS - Resources
CoS - Other
Cost of Sale (Sum of CoS)
Gross Margin  (Revenue - Cost of sale)
GM%
Direct Opex
Opex Resources
LRAIC Opex
Total Opex
Overheads
EBITDA (Gross Margin + Total Opex + Overheads)
EBITDA %
Depreciation
LRAIC Capex
EBIT (EBITDA + Depreciation + LRAIC Capex)
EBIT %

Is there any way I can manually code that in the backend/front end to make sure the dimension contains the right information.

Thank you very much.

1 Solution

Accepted Solutions
MVP
MVP

Re: Adding manual rows in a P&L table in Qlik Sense

Sure - here is a sample load script with an inline load of data that can of course be read from an Excel file or other database sources instead:

Screenshot from 2018-07-04 22-56-49.png

Screenshot from 2018-07-04 22-57-33.png

Screenshot from 2018-07-04 22-57-44.png

I have attached the Qlik Sense Application too.

4 Replies
MVP
MVP

Re: Adding manual rows in a P&L table in Qlik Sense

It is possible to get a P&L that looks like this:

2018-07-04 16_45_44-Microsoft Edge.png

But you can't get bolded text - so you will have to use the background color as a way of highligthing different levels instead. Does this satisfy your requirements?

With this you can also specify simple calculations like Cost of Sale should be sum of the above three rows:

       RangeSum(Above([2017],1,3))

And  the Gross Margin (Revenue - Cost of sale):

     Above([2017],5)-Above([2017],1)

Each individual row can have it's own specific expression independent of the others.

juanjulian_hs
New Contributor II

Re: Adding manual rows in a P&L table in Qlik Sense

Yes it does! Thank you so much Petter. Would you mind sharing what is the most efficient way to code that please?

Thank you!

MVP
MVP

Re: Adding manual rows in a P&L table in Qlik Sense

Sure - here is a sample load script with an inline load of data that can of course be read from an Excel file or other database sources instead:

Screenshot from 2018-07-04 22-56-49.png

Screenshot from 2018-07-04 22-57-33.png

Screenshot from 2018-07-04 22-57-44.png

I have attached the Qlik Sense Application too.

michael_nordstr
Contributor

Re: Adding manual rows in a P&L table in Qlik Sense

If you want the bold text and more layout options you can take a look at our Finance Report extension for Qlik Sense. It will cost you a small monthly fee but it's a completely different experience compared to native Qlik Sense or the free extensions out there. More features, better performance and it also comes with a very competent template to get you started quickly and save many hours of development. The Climber Finance Report 2.0 for Qlik Sense | Michael Nordström - YouTube