$-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.

[hic]

The $-expansion is made on a global level; before the chart is expanded, i.e. not once per record. This means that

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

isn't evaluated the way you want.

Bottom line is that you cannot use $-expansions this way.

HIC

[#3]

thanks a lot

but what about

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

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

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

? should be accurate, and indeed the $(=) can here be evaluated globally

[#4]

Is it that generally speaking the field upon which aggregation is made in AGGR() is evaluated globally, and not by rows?

[marcus_sommer]

Aggr is a normal function and will be evaluted by row and how Henric Cronström hint is $(=Expression) a variables which are always global - therefore try to simplify your approaches, perhaps with additionally flag-fields within the script or use Refer to Another Dimension in a Dimension in Pivot Table

- Marcus

[hic]

The Aggr() is not normal function. It is more like a For-Next loop, that creates an array of values.

The first parameter of the Aggr() function will be evaluated per distinct value of the second (and third) parameter. Hence, the first parameter must be an aggregation function (e.g.Sum(), Count(), Min(), Max(), Concat(), ...).

Example:

   Aggr( Count(distinct OrderID), CustomerID )

will create a dimension with the the number of orders per customer: 0, 1, 2, 3, etc. The count function will be evaluated once per customer.

See more on Aggregations and Function Classes.

HIC

[#7]

Thanks a lot!

What about this: I am aggregation concat(), which sounds reasonable. But I am appending the 'Year' only under some conditions.

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

$(=pick(1+match(...,...,...),...,...,...)))

this is however not working. is the syntax correct?

[marcus_sommer]

There is again $(=pick(1+match(...,...,...),...,...,...)) which wouldn't probably return a valid Field(list) and if it did then it is the same for all rows.

- Marcus

[#9]

thanks

but, I tested separately as another calculated dimension

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

and this is working fine...

so it seems to me that the $(=) is evaluated correctly...

[#10]

Could you explain what

Aggr( Concat (IF...

is actually doing, as I wrote in the precedent post ?

Thanks in advance