Aggregation AGGR with Concat() in Calculated Dimension

Wed, 16 Jul 2014 09:45:43 GMT

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

Re: Aggregation AGGR with Concat() in Calculated Dimension

Gabriel — Wed, 16 Jul 2014 15:36:59 GMT

Try Aggr(Concat(Year,';'),Account)

topic Re: Aggregation AGGR with Concat() in Calculated Dimension in QlikView

Aggregation AGGR with Concat() in Calculated Dimension

Re: Aggregation AGGR with Concat() in Calculated Dimension