Add static dimension value in a pivot table

[#1]

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

Thanks in advance for your thoughts.

Regards,

Rishi

[#2]

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

a bit more info would be good on how your fields are linked and how the data looks

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

[#3 · Accepted solution]

Hi,

Use concatenate.

if you are using (say)

load catagory,

         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

[#4]

Hi Joe and Shubhu,

Thank you for your replies.

@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

[#5]

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

[#6]

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