5 Replies Latest reply: Aug 31, 2011 9:49 AM by Jairish Ramsanjhal

# Add static dimension value in a pivot table

Dear all,

I have a dimension called Category and have some postal codes within it.

What I would like to do in this dimension is to add new values in the category like A102, A104 based on age to make some calculations with this new dimension in the expression field. I know I can do this with add calculated dimension but I can not manage this. I tried using this code

=IF([Age client]>=18,'A102',

IF([Age client]<18,'A104',[Category]))

But then only A102 and A104 remain as dimension while the others A101, A103 and A107 are gone because they fall outside the criteria of age. How can I achieve this?

The dimension looks like this:

Category

A101

A103

A107

And ultimately should look like this:

Category

A101

A102

A103

A104

A107

Regards,

Rishi

• ###### Add static dimension value in a pivot table

Hi,

are you saying that A101, A103 and A107 have a null [Age Client]? or how are they falling outside the range? If any of those values has an age client it will be caught in your expression and renamed to A102 or A104 accordingly

if it is the case that the age client is null for those 3 you will need something like

=IF([Age client]>=18,'A102',

IF([Age client]<18,'A104',If (isnull([Age Client]),[Category])))

thanks

Joe

• ###### Add static dimension value in a pivot table

Hi,

Use concatenate.

if you are using (say)

codes

from ac.qvd;

concatenate

load if([age client] > 18, 18) as catagory,

if([age client] > 18, 'A012') as codes

from xyz.xls;

use excel for such push thing.

HTH

Reg,

Shubhu

• ###### Re: Add static dimension value in a pivot table

Hi Joe and Shubhu,

@Shubhu I would prefer not to push from excel but add the dimensions within the chart based on expressions.

For more details I have attached the QVW file. Any thoughts are appreciated.

Regards,

Rishi

• ###### Re: Add static dimension value in a pivot table

Hi Rishi,

from looking at your qvw it seems you are trying to duplicate up your values so that they appear in the expression both for the calculated A102/A104 and then your standard field values too, I'm not sure if this can be done with just a simple calulated dimension alone.

If that is what you are after I would agree with Shubhu, you need to do the calculation in your data load so there is the extra line in category and revenue for you to use.

thanks

Joe

• ###### Re: Add static dimension value in a pivot table

Hi Joe and Shubhu,

Thank you for your replies and help. It's working the way you suggested :-)

Too bad you can't do this in the pivot itself.

Regards,

Rishi