14 Replies Latest reply: Apr 1, 2014 12:13 PM by Anand Chouhan

# Calculated dimension

Hi,

I need help, I want to reduce values in my dimension based on another dimension.

Lets say that I have Accounts 100, 101, 102 ,200, 201, 202.

These Accounts is grouped like A=100,101, B=200,201

Now I want to show Accounts based on Group A and B.

This will gives me Account 100, 101, 200, 201 in my dimension Account.

• ###### Re: Calculated dimension

Then you need to have a grouping field, a table like this:

Account, AccountGroup

100, A

101, A

102, A

200, B

201, B

etc

and filter on AccountGroup

• ###### Re: Calculated dimension

I have, and this is in a pivot table.

I need to use calculated dimension.

• ###### Re: Calculated dimension

I don't understand. if you have it, then why don't you use in the dimension? Why do you need a calculated one?

• ###### Re: Calculated dimension

Because I dont want to se 102 and 202 in my dimension.

• ###### Re: Calculated dimension

Then use the grouping field as dimension.

• ###### Re: Calculated dimension

A:

[

Accounts

100

101

102

200

201

202

];

Main:

*,

if(Match(Accounts,100,101,102),'Group A',

if(Match(Accounts,200,201,202),'Group B')) as GroupAccount

Resident A;

DROP Table A;

Hope this helps

• ###### Re: Calculated dimension

This is not script issue, I have all tables and the grouping already.

• ###### Re: Calculated dimension

You can use as below...

Account,

IF(Left(Account,1)=1,'A','B') as Group

Inline

[

Account

101

102

103

201

202

203

];

• ###### Re: Re: Calculated dimension

This is my table and I dont want to se Account that dont have a Group.

• ###### Re: Re: Calculated dimension

if you use only Group as a dimension and check Suppress null you will only see values with Group.

if you want to Account as Dimension, the create a calculated dimension : If(not isNull(Group), Account)

• ###### Re: Re: Calculated dimension

Hi Ioannis,

Yes this solve my problem, I use Account as a first Dimension and Group as second. Thats why I need to "hide" null in Group filed. When I use Group as first Dimension there is no problems. I also need to suppress null.

Thanks

• ###### Re: Calculated dimension

If you have selected Group in dimension, tick Suppress when value is null..

If Group is not in your dimension....

Use below as calculated dimension

IF(Len(TRIM(Group))=0,Account

• ###### Re: Re: Calculated dimension

Use suppress null value then it removes the blank values and only view the value which is not blank value.

Hope this helps

• ###### Re: Calculated dimension

What is the exact required output you want?

can you be more specific please !