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: 
samu
Partner - Contributor
Partner - Contributor

Making profit & loss statement with Qlikview

I'm making a profit & loss statement with qlikview and I'm wondering which would be the best approach. The problem I'm having is that I cant seem to find a proper way to create a table in Qlikview which would display all the information correctly. Ie.

Turnover (should display the total sum from all the accounts between XXXX and XXXX)

Other operating income

Materials & Services

Personnel expenses

etc...

I have data from our SQL database and all the transactions from our general ledger. I can get everything out just fine on account level. However, I'm having problems with grouping data from certain accounts together. (ie. all the sales accounts under turnover, all the purchase accounts under materials & services, etc.).

Do I have to do this when loading data to Qlikview from our database by creating for example a new calculated field that has the information under which type of costs or income specifics accounts belong to, or can I do it in some other way? I've tried to create a chart with different dimensions/expressions in Qlikview which would contain all the information but I naturally can't get all the lines to align.

The only way I've managed to do it in Qlikview is by adding separate charts on top of each other that only display one line of information but it doesn't seem to be all that convenient solution.

Any tips on how I should get this done?

Best regards,

Sam

16 Replies
Not applicable

Hi Samu

My applications are mainly accounting apps and I found two ways of doing in the script.

1. Hierarchy (see my example excel)

a. You make a hierarchy where you create groups called sales, purchase, materials & services etc and for each group, you add the General Ledger accounts under the group. For a hierarchy, you need to be very very careful of your parent id and your node id.

b. Make two lists - node id list and an adjacent node list.

c. In your script, create your hierarchy using the following code



ExpandedNodes:

HIERARCHY

(NodeID,ParentID,NodeName)
LOAD

NodeID,
ParentID,
NodeName
FROM
Source file.xls (biff, embedded labels, table is AdjacentNodes$)
;




d. When you make your charts, choose the "parents" like sales or purchase and only the GL accounts under the parent will be chosen. You can then sum up as you will.

2. Tagging

This is a much easier alternative if you only have 3-5 categories. For each GL entry,flag the account as either "sales", "purchases", "material & services"

1. Eg


LOAD
GL_Account_number,
IF(GL_Account_number < '70000', 'Sales','Purchases') AS Type


2. When you make a chart, you add your IF statement either in the dimension ark or in your expression itself

eg in a dimension


IF(Type='Sales',GL_Account_number)


You will then only choose the relevant dimension and the accounts having that dimension value.

Hope my solutions are useful. If anyone else in the forum has a more elegant way please let me know! I am always ready to learn!

Not applicable

Hello All,

Once, I have seen a web session that explains how to create a report P&L in QlikView.

Question to QlikTech: Can you make this web session available for all users?

Maybe someone can create a Wiki article?

Kind regards,

JTPro

Not applicable

Hi,

An example I received from a friend.

samu
Partner - Contributor
Partner - Contributor
Author

Thank you for your answers. Unfortunately I'm still using Qlikview personal edition as we're still in the mids of the desicision process whether to take this software.

Could someone please post a quick recap on how the P&L is done in the example wernbrenk just posted?

-Sam

Not applicable

Hi,

It's probably not the best example especially from a data perspective. You would do well to consinder the post by Jeanne Petersen.

The theory is simple. Every transaction was done in a specific GL Account. Each GL Account belongs to a certain group, which might in turn belong to Profit or GP etc. You want to create an in memory representation of this

LineID , Drawer , Group , GL Account

1, Sales , ProdClass1, 11100023

2, COGS ,ProdClass1, 21100023

2, COGS , ProdClass2, 21100024

TrxnID , GL Account (COGS) , Item , Amount

333 , 21100023 , A01 , 5000

334 , 21100024 , B07 , 8000

When you create you P&L in the graphs you'll use set analysis to refer to the Drawer (COGS, Sales,etc). When the drawer is selected it means that every subtransaction will also be selected and the sum will be done on that. In the example the sum(Amount) where Drawer='COGS' will be 13 000. The LineID will be used to format you P&L. You could also use your GL Account's for that.

QV is an amazing tool. Just take it!

Regards,

Not applicable

Where do you guys typically get your P&L hierarchy/grouping from?

Do you just spend the time setting it up in a spreadsheet or do you pull it directly from your Accounting-system and possible FSRow-tables in the system?

I really don't like the spreadsheets cause after a while you tend to end up with so much maintenance, even though it might seem like an instant quickstart.

Not applicable

Hi,

It will depend on your accounting system. I'm a SAP Business One Consultant and I get it directly out of SAP. I also know of other system where it has to be created in an Excel sheet. It all depends on the system you're using.

Regards

Not applicable

Ok. Just being curious, how is the hierarchy maintained in SAP, through row definition tables or by "branding" each account (i.e. informal segments to the GLAccount besides the ordinary natural,company,division,costcenter....) ?

Not applicable

By using Parent-Child relationships. 8 drawers have been hardcoded (Assets,Liabilities,Capital & Reserves, Turnover, etc.) Each account has an account code and a parent acc code. Accounts can go up to 5 levels deep.