# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for
Did you mean:
Creator II

## aggr functions

Hi guys,

I have two tables shown as below:

rep:
[
SalesRep, Customer, ZipCode, County
M, A, 00011, NY
M, A, 00022, NY
M, A, 00033, NY
M, A, 00044, NY
M, A, 00055, CA
N, B, 00011, NY
N, C, 00077, CA
];

sales:
[
Customer, Sales
A, \$100
B, \$200
C, \$250
];

The pivot table ends up to be like this:

But I would like to see Customer A in CA with \$20 (1/5 zip codes for A in CA) and Customer A in NY with \$80 (4/5 zip codes for A in NY). I'm trying to write expressions like:

Sum(Sales) / sum(aggr(count(ZipCode),Customer)) * sum(aggr(count(ZipCode),Customer,County))

But it's not giving me correct results:

I'm wondering how should I fix this. Any advice helps! Thank you in advance!

1 Solution

Accepted Solutions
MVP

```Sum(Sales)
*
Count(DISTINCT ZipCode)
/
Count(DISTINCT TOTAL <SalesRep, Customer> ZipCode)```
12 Replies
Creator II

Try this one

Sum(Sales) / (count(Total <Customer>ZipCode)) * count(Total<Country>ZipCode))

Creator II
Author

Hi, thanks for replying. It's not working.

Creator II

Please just use only below expression

=Sum(Sales) * (Aggr(count(ZipCode),County,Customer)/count(Total <Customer>ZipCode) )

MVP

Try this

```Sum(Sales)
*
Count(DISTINCT ZipCode)
/
Count(DISTINCT TOTAL <SalesRep> ZipCode)```
Creator II
Author

It seems it's not working for Total.

MVP

Or this

```Sum(Aggr(

Sum(Sales)
*
Count(DISTINCT ZipCode)
/
Count(DISTINCT TOTAL <SalesRep, Customer> ZipCode)

, SalesRep, County, Customer))```
Creator II
Author

Hi Sunny,

Thank you for your reply! Your second reply works, but I have large real data and will take too long time, so I'm willing to use the first one. I get what you mean in the first expression, but sorry for another question: In the real data I'm working on, SalesRep M may also have customer D in NY. In this case, numbers for D will mess up:

Could you please kindly check how I can fix that?

Creator II
Author

my data with D is this:

rep:
[
SalesRep, Customer, ZipCode, County
M, A, 00011, NY
M, A, 00022, NY
M, A, 00033, NY
M, A, 00044, NY
M, A, 00055, CA
M, D, 00022, NY
N, B, 00011, NY
N, C, 00077, CA
];

sales:
[
Customer, Sales
A, \$100
B, \$200
C, \$250
D, \$500
];

Partner

Customer D is correct if you use Sunny's second suggestion

Sum(Aggr(

Sum(Sales)
*
Count(DISTINCT ZipCode)
/
Count(DISTINCT TOTAL <SalesRep, Customer> ZipCode)

, SalesRep, County, Customer))

Tags
Community Browser