aggr functions

[wanyunyang]

Hi guys,

I have two tables shown as below:

rep:
load * Inline
[
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:
load * Inline
[
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!

[wanyunyang]

Yes, but as I said it would take too long time since my real data is large. I'm trying to see if I can do it with Sunny's first suggestion.

[sunny_talwar · Accepted solution]

How about this

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

[wanyunyang]

Works! Thank you!