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!
How about this
Sum(Sales) * Count(DISTINCT ZipCode) / Count(DISTINCT TOTAL <SalesRep, Customer> ZipCode)
Try this one
Sum(Sales) / (count(Total <Customer>ZipCode)) * count(Total<Country>ZipCode))
Hi, thanks for replying. It's not working.
Please just use only below expression
=Sum(Sales) * (Aggr(count(ZipCode),County,Customer)/count(Total <Customer>ZipCode) )
Try this
Sum(Sales) * Count(DISTINCT ZipCode) / Count(DISTINCT TOTAL <SalesRep> ZipCode)
It seems it's not working for Total.
Or this
Sum(Aggr( Sum(Sales) * Count(DISTINCT ZipCode) / Count(DISTINCT TOTAL <SalesRep, Customer> ZipCode) , SalesRep, County, Customer))
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?
my data with D is this:
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
M, D, 00022, NY
N, B, 00011, NY
N, C, 00077, CA
];
sales:
load * Inline
[
Customer, Sales
A, $100
B, $200
C, $250
D, $500
];
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))