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