Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
How about this
Sum(Sales) * Count(DISTINCT ZipCode) / Count(DISTINCT TOTAL <SalesRep, Customer> ZipCode)
Works! Thank you!