Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sjhussain
Partner - Creator II
Partner - Creator II

Combine values from Two Dimensions in the same table

‌Hello,

Hope all is well with everyone.

How can we combine values from two different dimension from while keeping the relationship.

We are working on a dashboard for GL - where we have 3 level of Accounts. 


I have two dimensions from the same table, ACCTID_L2_DESC and ACCTID_CUST


combinetwodimensions.jpg

As you can see COS in linked to 7 values in ACCTID_L2_DESC.  What we want to do is to have COS in ACCTID_L2_DESC and when we select COS then it should like to the same 7 values in ACCTID_L2_DESC field.

combinetwodimensions1.jpg

Will appreciate if someone can assist?

Thanks.

10 Replies
swuehl
MVP
MVP

I am probably just not understanding what you want to achieve.

If you somehow add a value COS to ACCTID_L2_DESC, and select this single value, all other values in this field will be excluded (greyed out).

sjhussain
Partner - Creator II
Partner - Creator II
Author

Stephen,

Right now COS is in the ACCTID_CUST Dimension and is linked to the other dimensions.

What we want to do is to have COS in ACCTID_L2_DESC and it should maintain the same links.  Meaning that when we select COS it should display the 7 values link.

combinetwodimensions2.jpg

So when we are using set analysis then for COS it should add all the amounts related with ACCTID_L2_DESC

Hope this is clear?

Thanks.

swuehl
MVP
MVP

Not really. How do you want to create associations between field values within the same field?

sunny_talwar

Are you imagining something like this?

Capture.PNG

I used this script:

Table:

LOAD ACCID_CUST & '-' & ACCTID_L2_DESC as Hierarchy,

  *;

LOAD * Inline [

ACCID_CUST, ACCTID_L2_DESC

COS, Bussiness Trips

COS, COGS

COS, Commission/Business

COS, Misc.

COS, Personnel Cost

COS, Rent

Revenue, Revenues

COS, Utilities

];

and then to create the tree view list box, I selected Tree-View in the list box properties on the general tab

Capture.PNG

This might not be exactly what you wanted, but this might work.

Best,

Sunny

sjhussain
Partner - Creator II
Partner - Creator II
Author

Stephen


What I want to do is the following.


Dimension: ACCTID_L2_DESC

Expression: sum(Amount)


Now for COS what should be displayed  is the sum of all the amounts of the 7 values which are available like personnel cost, misc. etc


Hope this is mich clear now.


Thanks



rrsrini2907
Creator
Creator

Hi Syed,

Do you want to create report as like below picture?

GL Report.png

Regards,

Srini.

swuehl
MVP
MVP

Either use e.g. a pivot table with two dimensions, just like Srinivasan showed, or create a table that defines your new, single dimension with a total like:

LOAD ACCTID_L2_DESC, ACCTID_L2_DESC as NewDim

RESIDENT YourTable;


CONCATENATE

LOAD ACCTID_L2_DESC, 'TOTAL Revenue' as NewDim

RESIDENT YourTable

WHERE ACCTID_L2_DESC = ' Revenues';


CONCATENATE

LOAD ACCTID_L2_DESC, 'TOTAL COS' as NewDim

RESIDENT YourTable

WHERE ACCTID_L2_DESC <> ' Revenues';


Here you are creating multiple records per ACCTID_L2_DESC value, once for the detailed line and once to create an association for the total.

sjhussain
Partner - Creator II
Partner - Creator II
Author

Sunny,


Thanks for the message. What we want is to have one list in which COS is there and it would have the sum of all the other values.


Same as u meantioned but we want COS in the same hierarchy as the others on ACCTID_L2.



rrsrini2907
Creator
Creator

Hi Syed,

Try this:

GL_Details:

LOAD * Inline [

ACCID_CUST, ACCTID_L2_DESC, GL_Amount

COS, Bussiness Trips, 1200

COS, COGS, 500

COS, Commission/Business, 275

COS, Misc., 350

COS, Personnel Cost, 550

COS, Rent, 2500

Revenue, Revenues, 10000

COS, Utilities, 750

];

Concatenate (GL_Details)

LOAD ACCID_CUST,

  ACCID_CUST as ACCTID_L2_DESC,

  Sum(GL_Amount) as GL_Amount

Resident GL_Details

Group By ACCID_CUST;

Regards,

Srini.