Account Grouping for P&L / BS

[#1]

Dear All,

For P&L / BS needs, I'd like to group source data consisting of accounts, their parets and amounts into "nice-to-analyze" structure. The attached example shows the details.

I'll be grateful for any help.

PS I couldn't find a possibility to upload qvw example. Has it been disactivated?

BR,

Przemek

[#2]

your table must be like this

test:

load

Account_id,

Account name,

Pernrt_id,

Amount

from tab1;

Now-----------------

load

Account_id

Accout name

resident test;

load

Pernrt_id as Account_id,

Amount

resident test;

drop table test;

[#3]

Thanks for your answer, Anwar.

This script, however, allows to build only one level of the structure but I need to build 4 levels (groups). Do you know how to "extend" the script to 4 levels as in the attached example?

BR,

Przemek

[#4]

Research semantic links in the scripting documentation. It should do exactly what you are looking for.

[#5]

What I did for a P&L report was to build an excel template where I specify the hierarchies and then use Intervalmatch on the accounts. Should be fairly easy in this case

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3755.P_2600_L_5F00_Template.xls:550:0]

Then use

Intervalmatch (Account_Id) LOAD Account_id_start , Account_id_end RESIDENT P&L_Template;

[#6]

Isn't this the exact situation in Book 1, 27.5 of the documentation? It suggests using Hierarchy and Hierarchybelongsto

Or does that not work?

[#7]

I've checked tutorials and found nothing of any help. QV's "Help" says too generally for me to introduce the function. What scripting documentation did you mean and could you tell where to find it?

BR,

Przemek

[#8]

jhpark's answer is exactly correct. Book I, Part IV, 27.5 (Page 487) is exactly where the answer is.

The link to the documentation is http://ftp.qliktech.com/QvWebDownloads/Documentation/QlikView%208.50%20Reference%20Manual%20ENG.pdf

[#9]

So let's extend this by 1) Creating a group that links together the multiple levels of the hierarchy

2) putting values in at levels above the bottom level.

node parent value
A - 100
AA A 10
AB A 20

Right now, the value for A shows up as 130 (sum of A, AA, and AB), where I would rather have it show up as 100. ie, the value I assigned explicitly for A is the entire value I want for A -- I don't want AA and AB rolling up to A as well. How can that be accomplished?

(This happens whether I use a cyclic group or a drill down group.)

[#10]

I have also used this structure, which seems to be very nice.

Unfortunately I am confronted with some design problems.

I have my P&L now splitted into the different departments (the columns).

When I use the "if(RowType=1, RGB(10,10,10))" for the background of the dimension, I ONLY GET cells with amounts COLOURED. So Cells without figures or filled with Zero ARE NOT COLOURED, which i would like to change 😕

Could anybody additionally tell me how to get the headers bold ? I have the Departments in the columns, so just setting "Text" to "bold" is not working 😞

I would be very grateful for any responses!

regards,

Christoph