Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

How to Create a Profit and Loss Statement in QlikView

Employee
Employee

How to Create a Profit and Loss Statement in QlikView

Have you ever created a P&L statement in QlikView?  Well, this technical brief outlines how to create a P&L statement in QlikView 11 using IntervalMatch.

Also check out this blog post.

Thanks,

Jennell

Labels (1)
Attachments
Comments
Employee
Employee

Hi,

I am not sure I understand your question.  It looks like the data you have are the subtotals.  Is that correct?  If so, yes you can work with that, you will just not have the accounts/values that make up the subtotal values.

Kind Regards,

Jennell

Employee
Employee

Hi Paul,

I have added a ZIP file to the original post titled "Excel Files.zip" which includes all the files I used in creating the example app.  I hope this helps.

Kind Regards,

Jennell

Not applicable

Hi Jennell

Thanks a lot for posting such a frequently needed topic. I was looking exactly for such help..

Can you please upload Personal Edition enabled QV Doc?

BR

SAK

Employee
Employee

SAK,

I have uploaded the "How to Create a Profit and Loss Statement.qvw" in the original post with an embedded license so you should be able to download and view the QVW now.

Kind Regards,

Jennell

sebastiandperei
Valued Contributor

Hi Janell, thanks for this solution.

It is a very easy and elegant way to resolve the most simple P&L reports.

Thanks!

spyrosd80
New Contributor II

Hi Janell,

Your solution is great!!!

I have a question for you.

Is it possible to add a column where I can calculate the % of Sales for Gross Sales,Sales Return, Off Invoice Discounts and Sales Promotions?

In other words Gross Sales/Net Sales, Sales Return/Net Sales etc ?

Thank you!!!!

Employee
Employee

Sorry for the delay. 

In the original post (above) I added a ZIP file named How to Create a Profit and Loss Statement - Percentage.zip showing an example of how you can add the gross sales/net sales. 

In the Excel file, I added a row for this new heading as well as a flag and a format column. 

In the QVW, I added a key field to the Account Balance table named PeriodKey and I added all the script on the Main 5 tab where I created a new table that stores the net sales value.  In the chart on the UI, I modified the Actual expression to either calculate the sales value or the percentage.

Hope this helps!

Jennell

Not applicable

Hi Janeell,

It's an excellent paper posted by you. My query is also the same as of spyrosd80, as to how to calculate column for % of sales for all the P&L Items.

Thanks

Saurabh

Not applicable

Thanks for this Jennell.  It has really helped me move forward with an assignment.

I am having one difficulty though and that is preserving the blank rows within the chart.  I followed the sample and have compared the provided QVW side by side with the one that I created and cannot see what I'm missing.  It's simply rolling each of the 'blanks' up to a single row at the top of my chart.  I confirmed that it is doing this for each of the 's' rows in my spreadsheet by putting in a counter value.

Any further insight into how this aspect of it should be implemented?

Thanks,

Lorna

Employee
Employee

Hi Lorna,

There are 2 things I would check:

  1. In the Excel file that has the format for the statement (ExecProfitLoss.xls in my example), make sure you have an 's' in the Exec P&L Level column if the row should be blank.
  2. In the QVW expressions, use an if statement to see if Exec P&L Level = 's', if it does, display an empty string otherwise display the calculation.  Here is an example: if([Exec P&L Level] = 's','',Sum({<[Fiscal Year]={$(vYTDYear)} >} [Amount])).

Hope this helps.  Good luck!

Jennell

Version history
Revision #:
1 of 1
Last update:
‎08-10-2012 09:56 AM
Updated by:
Employee