Qlik Community

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

Community Browser