Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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))
Hi Michael, thank you very much this is the right solution!!!!