How to Create a Profit and Loss Statement in QlikView

[Jennell_McIntire]

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

[Jennell_McIntire]

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

[Jennell_McIntire]

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

[#4]

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

[Jennell_McIntire]

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]

Hi Janell, thanks for this solution.

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

Thanks!

[spyrosd80]

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!!!!

[Jennell_McIntire]

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

[#9]

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

[#10]

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

[Jennell_McIntire]

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