topic Sum(Aggr(Count)) ? in App Development

Sum(Aggr(Count)) ?

danimarc12 — Thu, 30 Sep 2021 09:17:48 GMT

Hello everyone!

I have an issue with the following Pivot Table.

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!

Re: Sum(Aggr(Count)) ?

jbhappysocks — Thu, 30 Sep 2021 09:28:42 GMT

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.

Re: Sum(Aggr(Count)) ?

danimarc12 — Thu, 30 Sep 2021 09:34:21 GMT

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.

Re: Sum(Aggr(Count)) ?

Or — Thu, 30 Sep 2021 09:50:02 GMT

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.

Re: Sum(Aggr(Count)) ?

danimarc12 — Thu, 30 Sep 2021 10:01:08 GMT

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

Re: Sum(Aggr(Count)) ?

Or — Thu, 30 Sep 2021 10:02:32 GMT

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?

Re: Sum(Aggr(Count)) ?

danimarc12 — Thu, 30 Sep 2021 10:18:32 GMT

Yes, it looks is working, thank you!