Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sudhakaran_abha
Contributor

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

Tags (1)
3 Replies
neilhepburn
New Contributor II

Re: Set Analysis - To avoid double counting

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
Contributor III

Re: Set Analysis - To avoid double counting

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
Contributor II

Re: Set Analysis - To avoid double counting

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

Community Browser