Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sudhakaran_abha
Creator
Creator

Set Analysis - To avoid double counting

Here is a sample table (Attached)

  

Op_IDOp_NameOp_LeadTeam_MemberTeam_RoleEmp_IDSales
1ABCJohnAlanLeadA10010
1ABCJohnAbhaySupportA20010
1ABCJohnSamSupportA30010
1ABCJohnJohnSupportA50010
1ABCJohnJohnLeadA50010
2DEFRonKyleSupportA40020
2DEFRonRussellSupportA60020

Each opportunity has a Op_Lead and multiple team members.

What I want:

To Sum Sales of Opportunity Records if a person is Op_Lead OR is listed as a Lead under "Team_Role"


For example in Op_ID 1 we should get $10 Sales amount for John, because he is list as a lead under Team_Role and his name is also listed as Op_Lead.

Similarly for Op_ID 2, we should get $20 for Ron as he is listed as a Op_Lead,  even though he is not listed under Team_Roles


Wondering how to achieve this ? Any help is appreciated. Thanks

3 Replies
neilhepburn
Partner - Contributor III
Partner - Contributor III

I suggest you remodel your data into three (3) tables: Opportunities; Staff; Staff Opportunity Role.  Using your data, the tables would be modeled as:

Opportunities:

Op_ID,Op_Name,Sales

1,ABC,10

2,DEF,20

Staff:

Emp_ID, Staff Name

A100,Alan

A200,Abhay

A300,Sam

A400,Kyle

A500,John

A600,Russell

AXXX,Ron

[Staff Opportunity Role]:

Emp_ID,Op_ID,Role

1,A100,Lead

1,A100,Lead

1,A200,Support

1,A300,Support

1,A500,Support

1,A500,Lead

2,A400,Support

2,A600,Support

2,AXX,Lead

Please note: Due to the way the data would be loaded, John is duplicated in the bridge table as Lead.  However, this will not make any difference to totals assuming you are only using the [Staff Opportunity Role] table as strictly a bridge table. But if this is an issue, the [Staff Opportunity Role] table can be easily de-duplicated during load time.

vkish16161
Creator III
Creator III

Try this:

Sum (Total <Op_Lead,Op_ID> distinct Sales)    // Considering Op_Lead,Op_ID are chart dimensions

or

Sum

(

Aggr (

Sum(distinct Sales)

,


Op_Lead , Op_ID )

)

jayanttibhe
Creator III
Creator III

Your table satisfy all the conditions you explaining.... need more data which clearly differentiate between positive and negative scenarios to construct the Set Analysis