New Dimension in Pivot Table - select one of two dimensions

[#1]

I would like to create a new dimension in a Pivot Table. This dimension takes the value of one dimension in all cases except if this value is 'OTHER'. In this last case, the new dimension takes the value of another dimension.

The syntax I thought of is

IF([Corporate Account] = {'OTHER'},[Sales Account],[Corporate Account])

This is not working: the Dimension do not even appear in the Pivot Table.

Is it an error in the test of the IF statement: [Corporate Account] = {'Other'}

Could you help with this ?

[#2]

Hi

try without curly braces

=if([corporateaccount]='other',sales account, corporate account)

thanks

padma

[#3]

thamks for your input, however this is not doing it. Any other idea?

[#4]

Hi,

The above answer should work (unless there's syntax or case-sensitive adjustments based on your model and data).

Another option could be to create this dimension within the script if possible, so it becomes a column in the table, so you won't need a calculated dimension in your pivot.

[#5]

thanks. In fact, the new dimension is not even displayed in the pivot table... as far as i know, there is no case / syntax mistake with the dimensions's expression.

what can be the cause?

[#6]

Can you attach a sample qvw ?

[#7]

Unfortunately not, I am working with Full Browser version

[Anonymous]

Hi

For things like this I sometimes slowly build the expression to find the joker.

As in first start really simple, and just display the field

     [Corporate Account]

Then include a simple if :

     IF([Corporate Account] = 'OTHER' , 1 , 0)

And finally :

     IF([Corporate Account] = 'OTHER',[Sales Account],[Corporate Account])

This should help you pinpoint the issue.

Best Regards,     Bill

[#9]

thank you for this!

Actually, even [Corporate Account] as a dimension is not working, no change is made to the pivot table...

[sorrakis01]

HI,

A possible solution:

You can create a variable.

Insert the 2 dimensions: [Sales Account], [Corporate Account]

IF([Corporate Account] = 'OTHER',variable = true,variable = False)

then in one dimension you write on conditional variable = true and in the other dimension variable = False,

Regards,