Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
petter
Partner - Champion III
Partner - Champion III

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.

View solution in original post

6 Replies
petter
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

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

Thank you!

petter
Partner - Champion III
Partner - Champion III

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
Partner - Creator
Partner - Creator

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

Notwo
Contributor III
Contributor III

Really appreciate your example! How would I go about to add additional years in the P&L?

lee_
Contributor
Contributor

HI Petter, 

I really like the solution you've written here

Due to system issues I can't open your QVF above. I don't suppose you've got a text/word version of the code I can play with and alter please.

 

Many thanks