I have a pivot table with a dimension Account. For each row in Account, there are several rows in dimension Transaction, as in the example here:
Account Transaction Year
Acc1 T1 2012
Acc1 T2 2013
Acc2 T21 2006
Acc2 T22 2008
Acc2 T23 2011
for each row in Transaction, I would like to get the list of all dates of transaction for the current account. For example, for the row of T1, I would like to get 2012,2013, for the row of T23, I would like to get 2006,2008,2011.
I try to use jointly AGGR() and Concat(): I aggregate with AGGR() over Accounts, and I Concatenate with Concat() all the years for each account in one string.
However. this is in fact a bit more complicated. I don't want to concatenate years for all rows, but year for rows which verify some conditions. I came up with this syntax:
Aggr(
Concat(
IF(MasterCodeDesc = 'BASE', IF([Product Type]= 'Sales', IF([Year] > 2009,Year))), ',')
,[Account])
I concatenate Year if the row verifies some condition, and aggregation is over accounts.
This is not giving the good result at all: it seems that for each account, if the first row (firs transaction) is verifying the 3 conditions, the year is concatenated. If the first row is not verifying the 3 conditions, all strings for this account (for all transactions) are empty.
What is Aggr(Concat( actually doing ? What sounds wrong in the syntax above ?
Thanks in advance
