Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Pivot Table (count distinct and total sum)

Hi all,

I would like to have a total on Pivot Table

I tried with dimensionality (without success)

Someone can help me please ?

Many Thanks

Claudio

1 Solution

Accepted Solutions
Or
MVP
MVP

As best as I can tell, you are not using aggr() correctly. Try the following:

sum(if(aggr(count(Order),CustomerId)=1,1,0) )


What we are doing is counting the number of orders - count(Order)

for each customerId - aggr(count(Order),CustomerId)

if this equals 1 (or 2, or 3, depending on column), we place 1 in our column, otherwise 0 - if(aggr(count(Order),CustomerId)=1,1,0)

Finally, we sum this total - this will not impact our regular lines, since we will just sum up 1 or 0, but in the Total line, this will allow it to count (sum of 1s) the number of customers - sum(if(aggr(count(Order),CustomerId)=1,1,0) )

I hope this clears things up (the same syntax also works for your second table).

View solution in original post

2 Replies
Or
MVP
MVP

As best as I can tell, you are not using aggr() correctly. Try the following:

sum(if(aggr(count(Order),CustomerId)=1,1,0) )


What we are doing is counting the number of orders - count(Order)

for each customerId - aggr(count(Order),CustomerId)

if this equals 1 (or 2, or 3, depending on column), we place 1 in our column, otherwise 0 - if(aggr(count(Order),CustomerId)=1,1,0)

Finally, we sum this total - this will not impact our regular lines, since we will just sum up 1 or 0, but in the Total line, this will allow it to count (sum of 1s) the number of customers - sum(if(aggr(count(Order),CustomerId)=1,1,0) )

I hope this clears things up (the same syntax also works for your second table).

Not applicable
Author

Many Thanks

Now work perfectly !!!

(I used "aggr" few times ! That's the problem)

Claudio