Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wanyunyang
Creator III
Creator III

aggr functions

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:

1.PNG

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:

2.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

How about this

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

View solution in original post

12 Replies
bharathadde
Creator II
Creator II

Try this one

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

wanyunyang
Creator III
Creator III
Author

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

3.PNG

bharathadde
Creator II
Creator II

Please just use only below expression

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

sunny_talwar

Try this

Sum(Sales)
*
Count(DISTINCT ZipCode)
/
Count(DISTINCT TOTAL <SalesRep> ZipCode)
wanyunyang
Creator III
Creator III
Author

It seems it's not working for Total.

4.PNG

sunny_talwar

Or this

Sum(Aggr(

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

, SalesRep, County, Customer))
wanyunyang
Creator III
Creator III
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:

 

5.PNG

 

Could you please kindly check how I can fix that?

wanyunyang
Creator III
Creator III
Author

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
];

mjtaft2017
Partner - Creator
Partner - Creator

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