$-sign Calculated Dimension syntax with condition

[#1]

I have a pivot-table with one calculated dimension of this form

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

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

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

If I test alone the field on which i aggregate,

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

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...)))

this is the right computation.

However,

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

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

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

gives Null() for all rows.

On the contrary,

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

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

pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

gives some results, but result is wrong: it is as if the IF in the condition in the $-sign expansion was never hit, whereas there are cases where Corporate Account is 'OTHER'

Would you have a hint ? How to fix this ?

Thanks in advance.

[marcus_sommer]

That an expression could be interpreted as calculated dimension or as single expression didn't meant that these expression returned a valid value as nested part from another expression - such things could work but you couldn't be sure without expensive testings.

It's not recommended to use such complicated structures within objects - although they may work - then the time exposure for creating, validation and maintance is compared with script-solutions (which simplify calculations and are reusable in other apps and/or objects) often too high.

I couldn't imagine what you are want to reach with your pick(match()) if it not returned the dimension-level from the row - for this could be dimensionality() used .

- Marcus