Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
andymanu
Contributor 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_IDProduct_NameRegionSalesSales ManagerSales Person 1Sales Person 2Sales Person 3Sales Person 4
P10AppleNorth30000AdamSiyaraAdam  
P11NokiaSouth25000BenDon   
P12SamsungEast35000DonDavidDon  
P10AppleSouth26000AdamTess   
P12SamsungEast30000BenDavidDon  

 

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_IDProduct_NameRegionSales PersonSales
P10AppleNorthAdam30000
P10AppleNorthSiyara30000
P10AppleNorthDian26000
P10AppleSouthTess26000
P11NokiaSouthBen25000
P11NokiaSouthDon25000
P12SamsungEastDon65000
P12SamsungEastDavid65000
P12SamsungEastBen30000

 

Could someone please help me with this issues?

Thank you in advance.

Kind regards,

Andy

1 Reply
Highlighted
andymanu
Contributor II

Re: Calculate sum of DISTINCT field value based on values of another columns

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

Appreciate your feedback.

Thank you.

Kind regards,

Andy