Nested aggregation in a table

[Anonymous]

Hi,

I am facing a big problem: I have to aggregate in my application on basis of a count statesmen. Follow one example:

  MY DATA:

Customer	Product	Day	ID
Giulio	Caffe	1	01
Giulio	Pizza	2	02
Giulio	Caffe	3	03
Giulio	Caffe	4	04
Tom	Caffe	1	05
Tom	Pizza	2	06
Tom	Pizza	3	07
Tom	Latte	4	08
Stefan	Latte	1	09
Stefan	Latte	4	10

I want to built the following table :

TABLE A:

Product	# Customer once	# Customer twice	# Customer Three times
Pizza	1	1	0
Caffe	1	0	1
Latte	1	1	0

In order to built the table above (my last deliverable) I have to pass for the following table:

Giulio Caffe 3

          Pizza 1

Tom   Caffe 1

          Pizza 2

          Latte 1

Stefan Latte 2

I try to summarize: I am able to create the above table with the follow expression aggr(count((distinct ID), Product, Customer) but in order to create the Table A I need a nested aggregation (?) because I need to count the number of customers that have try one service once, twice or three times.

Any help would be appreciated.

[Anonymous · Accepted solution]

I think the attached is what you need.

In case you can't open it:

dimension - Product

expressions

count(distinct if(aggr(count(distinct ID),Customer,Product)=1,Customer))

count(distinct if(aggr(count(distinct ID),Customer,Product)=2,Customer))

count(distinct if(aggr(count(distinct ID),Customer,Product)=3,Customer))

[Anonymous]

Hi Michael, thank you very much this is the right solution!!!!