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!
Example for hierarachy.xls 55.5 K
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.
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.