Combine values from Two Dimensions in the same table

[sjhussain]

‌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

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?

Thanks.

[swuehl]

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]

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.

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]

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

[sunny_talwar]

Are you imagining something like this?

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

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

Best,

Sunny

[sjhussain]

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]

Hi Syed,

Do you want to create report as like below picture?

Regards,

Srini.

[swuehl]

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]

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]

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.