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
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.
Will appreciate if someone can assist?
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).
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.
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?
Are you imagining something like this?
I used this script:
LOAD ACCID_CUST & '-' & ACCTID_L2_DESC as Hierarchy,
LOAD * Inline [
COS, Bussiness Trips
COS, Personnel Cost
and then to create the tree view list box, I selected Tree-View in the list box properties on the general tab
This might not be exactly what you wanted, but this might work.
What I want to do is the following.
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.
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
LOAD ACCTID_L2_DESC, 'TOTAL Revenue' as NewDim
WHERE ACCTID_L2_DESC = ' Revenues';
LOAD ACCTID_L2_DESC, 'TOTAL COS' as NewDim
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.
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.
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
ACCID_CUST as ACCTID_L2_DESC,
Sum(GL_Amount) as GL_Amount
Group By ACCID_CUST;