Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Account Grouping for P&L / BS

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,

Przemekerror loading image

Tags (1)
9 Replies
Highlighted
Not applicable

Account Grouping for P&L / BS

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;

Highlighted
Not applicable

Account Grouping for P&L / BS

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

Highlighted
Not applicable

Account Grouping for P&L / BS

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

Highlighted
Not applicable

Account Grouping for P&L / BS

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;

Highlighted
Not applicable

Account Grouping for P&L / BS

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?

Highlighted
Not applicable

Account Grouping for P&L / BS

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

Highlighted
Not applicable

Account Grouping for P&L / BS

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

Highlighted
Not applicable

Account Grouping for P&L / BS

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

Highlighted
Not applicable

Account Grouping for P&L / BS

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