Aggregation in Concat()

[#1]

Hi All,

I'm trying to build a pivot table that will show in one column aggregation result on one dim split by another dim like in the below example. I tried Concat(Type, Count(Date)) but it seems Concat() does not accept Count().

Input

-------

Name, Type, Date

John, A, 2011-08-11

John, B, 2011-08-11

John, B, 2011-08-12

David, B, 2011-07-29

Result

---------

Name, Value

John, A1, B2

David, B1

Regards,

Przemek

[swuehl]

Hi,

concat takes up to three parameter:

1. expression (i.e. the values to concatenate, often used with a  field name)

2. delimiter

3. sort weight

Your second parameter Count(Date) is not a valid delimiter.

Maybe try

concat( aggr(Type&Count(Date),Type) )

instead.

Regards,

Stefan

[Anonymous]

Przemek,
First, both concat() and count() are aggregation functions, so you can't use one within the other witjhout aggr().
Next, "one dim split by another dim" is apparently not what you really want, judging by the example of the desired result.  The delimeter is comma.
I think that the dimension is Name, and the expression is something close to what Stefan suggests:
concat(aggr(Type&count(Date),Type),', ')

[#4]

Thank you. I've checked the formula against the example and I got:

John A1, B3

David -

[gandalfgray]

Hi przemek

If you want to create a pivot chart with Name verticaly and Value horizontaly

you just need to group by Name,Type - no need to use concat:

Result:

Load Name,

    Type&Count(Date) As Value

Resident Input

Group By Name,Type;

Here's the pivot chart you get:

hth/gg

[#6]

Yes, though the data model requires that to put it in chart formula.

[gandalfgray]

I'm not sure I understand what you want, but you can try this then:

_Result:

Load Name,

    Type&Count(Date) As _Value

Resident Input

Group By Name,Type;

Result:

Load Name,

    Concat(_Value,', ') As Value

Resident _Result

Group By Name;

Drop Table _Result;

hth/gg

[swuehl · Accepted solution]

I see.

Try a

=concat(aggr(Type&count(Date),Type,Name),', ')

(adding the Name to the aggr() dimensions).

Regards,

Stefan

[#9]

This is it! Thank you, Stefan.