topic Making profit & loss statement with Qlikview in QlikView

Making profit & loss statement with Qlikview

samu — Fri, 17 Jul 2009 16:26:24 GMT

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

Making profit & loss statement with Qlikview

Fri, 17 Jul 2009 17:39:51 GMT

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!

Making profit & loss statement with Qlikview

Fri, 17 Jul 2009 18:34:18 GMT

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

Making profit & loss statement with Qlikview

Fri, 17 Jul 2009 18:41:27 GMT

Hi,

An example I received from a friend.

Making profit & loss statement with Qlikview

samu — Fri, 17 Jul 2009 19:34:56 GMT

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

Making profit & loss statement with Qlikview

Fri, 17 Jul 2009 19:56:23 GMT

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,

Making profit & loss statement with Qlikview

Fri, 17 Jul 2009 21:23:54 GMT

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.

Making profit & loss statement with Qlikview

Mon, 20 Jul 2009 13:40:32 GMT

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

Making profit & loss statement with Qlikview

Mon, 20 Jul 2009 20:31:53 GMT

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....) ?

Making profit & loss statement with Qlikview

Mon, 20 Jul 2009 20:53:15 GMT

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.

Making profit & loss statement with Qlikview

Mon, 20 Jul 2009 21:03:57 GMT

Ok. Thank you for your input!

Making profit & loss statement with Qlikview

samu — Thu, 23 Jul 2009 19:13:40 GMT

Thanks for the advice! I'm going to try to achieve the results I'm looking for by using Jeanne's sample.

Br,

Sam

Making profit & loss statement with Qlikview

Mon, 15 Mar 2010 15:50:06 GMT

Any example in this case will be appreciated.. Thankyou:-)

Re: Making profit & loss statement with Qlikview

Mon, 17 Dec 2012 12:39:16 GMT

Hello Jeanne,

I m still a newbie with QV.

I do alot of accounting stuff (ERP) and I m keen on using QV to handle P&L, Balance sheet, cash flow reports etc.

I ll appreciate any help you can give me to jump-start

Thanks in advance

Femi

Re: Making profit & loss statement with Qlikview

pelqlikview — Thu, 18 Jul 2013 15:36:15 GMT

Hi QlikView Guys,

we have developed an QliView AddOn for solving that problem - for more details have a look at: http://www.highcoordination.de/en/qlikview/qlikview-add-ons/structurebuilder/

Please feel free to contact me for further questions.

Peter Ellerau

HighCoordination GmbH

Re: Making profit & loss statement with Qlikview

Fri, 19 Jul 2013 17:51:32 GMT

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

Re: Making profit & loss statement with Qlikview

robert99 — Sun, 01 Sep 2019 05:03:04 GMT

I did this in Sense. But it would also work in View

I used it to not only do a number of P+Ls. but also a cash flow

https://community.qlik.com/t5/Qlik-Sense-App-Development/QlikSense-Profit-and-Loss-Statement-without-an-extension/m-p/1512744