# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (1)
1 Solution

Accepted Solutions
Valued Contributor II

## Problem with Pivot Table (count distinct and total sum)

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

2 Replies
Valued Contributor II

## Problem with Pivot Table (count distinct and total sum)

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

## Problem with Pivot Table (count distinct and total sum)

Many Thanks

Now work perfectly !!!

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

Claudio