Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

giulioandrea
New Contributor III

Nested aggregation in a table

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:

CustomerProductDayID
GiulioCaffe101
GiulioPizza202
GiulioCaffe303
GiulioCaffe404
TomCaffe105
TomPizza206
TomPizza307
TomLatte408
StefanLatte109
StefanLatte410

I want to built the following table :

    

TABLE A:

Product# Customer once# Customer twice# Customer Three times
Pizza110
Caffe101
Latte110

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.

Tags (2)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Nested aggregation in a table

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))

2 Replies
mov
Esteemed Contributor III

Re: Nested aggregation in a table

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))

giulioandrea
New Contributor III

Re: Nested aggregation in a table

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