Problem with Pivot Table (count distinct and total sum)

[#1]

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

[Or · Accepted solution]

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

[#3]

Many Thanks

Now work perfectly !!!

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

Claudio