Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danimarc12
Partner
Partner

Sum(Aggr(Count)) ?

Hello everyone!

I have an issue with the following Pivot Table.

Cattura.PNG

My measure is count(DISTINCT Cliente). If I want to sum for each Cliente the total of the count, like the example in the third line, I want to see the number 4 as the total of the sum, 

what's the correct expression? I've tried with sum(aggr(count(DISTINCT Cliente), Brand)),"Brand" is the dimension of the columns, but It's not working.

Can anyone help me?

Thanks to all!

1 Solution

Accepted Solutions
Or
Master II
Master II

What you're describing, near as I can tell, matches count(distinct Brand) for a pivot that has Customer and Brand as its dimensions. Have you tried it? Is it not working as you expect?

View solution in original post

6 Replies
jbhappysocks
Creator
Creator

Hello

It looks like you want to count Brand, not Cliente. In your example 4 is the no of distinct Brands for 1 distinct Client?

try count(distinct Brand) instead.

danimarc12
Partner
Partner
Author

Well the expression count(DISTINCT Cliente) is correct. Now I want to sum the values in the line.

Do you know how can I do it?

If I use the "Show totals" of the pivot table, I'm gonna display always 1.

Or
Master II
Master II

At the intersection of a single customer and a single brand in the pivot, there is always going to be one distinct customer and one distinct brand, so both should return the same result here. However, if you are counting distinct customer totals by customer, you're always going to get a result of one. You should be counting the brand, as @jbhappysocks  suggested. You could probably work around this using a more complex formula, but a simple count(distinct Brand) appears to meet your requirement.

danimarc12
Partner
Partner
Author

I'm gonna try to be more clear.

My goal is to display how many time I've sold a new Brand to a Cliente. As the image shows, I have sold 4 new brands to the Cliente "GLOBAL SHOP". The numbers are correct because I've checked but I would like to display also the total value of 4 (in this case).

Or
Master II
Master II

What you're describing, near as I can tell, matches count(distinct Brand) for a pivot that has Customer and Brand as its dimensions. Have you tried it? Is it not working as you expect?

View solution in original post

danimarc12
Partner
Partner
Author

Yes, it looks is working, thank you!