# App Development

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Creator II

## Calculate sum of DISTINCT field value based on values of another columns

Hi All,

Please find the attached sample data set for this question below;

 Sales Person Product_ID Product_Name Region Sales Sales Manager Sales Person 1 Sales Person 2 Sales Person 3 Sales Person 4 P10 Apple North 30000 Adam Siyara Adam P11 Nokia South 25000 Ben Don P12 Samsung East 35000 Don David Don P10 Apple South 26000 Adam Tess P12 Samsung East 30000 Ben David Don

My requirement is, I want to calculate total sales performed by each Sales Person (Including the Sales Manager).  for each product. However, in my data set the there are instances where the Sales Manager is duplicated as a Sales Person (for example, P10 in region "North" where the Sales Manager is duplicated into the Sales Person 2 column) and when calculating the individual sales by the Sales Person, I don't want to calculate the sales value for Adam as \$ 60000 instead, it should be \$30000.

I tried converting the data set in to a cross table but couldn't get the desired result. Assume, I have to use the "Aggr" function but not so  sure how to use it.

Desired output should be something like below;

 Product_ID Product_Name Region Sales Person Sales P10 Apple North Adam 30000 P10 Apple North Siyara 30000 P10 Apple North Dian 26000 P10 Apple South Tess 26000 P11 Nokia South Ben 25000 P11 Nokia South Don 25000 P12 Samsung East Don 65000 P12 Samsung East David 65000 P12 Samsung East Ben 30000

Kind regards,

Andy

Labels (2)

• ### Sum of fields

Creator II
Author

I assume something like below should work,

1. Converting the straight table in to a cross table,

2. Use a pivot table to visualize the data,

3.  Use an expression like,

=Sum(Aggr(Sum(DISTINCT Sales), [Sales Person], Product_ID, Region))