Unique Count with criteria under a distriubtion matrix

[silentbuddha]

Hi All,

I'm trying to figure out how to solve this Unique Count situation based on multiple criteria

Scenario: we have agents from different departments who produce different sales on different plan rates

Raw Data
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Plan Rate[/TD]
[TD]Payout pts[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]


Matrix

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Matrix
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Plan Rates
[/TD]
[TD="align: center"]Plan Rates
[/TD]
[TD="align: center"]Plan Rates
[/TD]
[TD="align: center"]Plan Rates
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"] Sales Volume bucket
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]count of unique # of employees who made one sale on a plan_rate of 10.[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"] Sales Volume bucket
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]count of unique # of employees who made two sales on a plan_rate of 20.[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"] Sales Volume bucket
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]count of unique # of employees who made three sales on a plan_rate of 30.[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[NeonRedSharpie]

What do you want the end data to look like?

 

[silentbuddha]

Hi Neon,

I just want a to know what formula I need in order to answer fill in the values within the matrix that i posted above.

my apologies if I was not clear in my original post.

Thanks

 

[silentbuddha]

Hi All,

Would anyone be able to point in me the right direction as to how I would need to approach this problem and solve it ?

thanks and much appreciated

 

[Aladin Akyurek]

Hi All,

Would anyone be able to point in me the right direction as to how I would need to approach this problem and solve it ?

thanks and much appreciated

Where is the information regarding 1 sale, 2 sales, etc?

 

[silentbuddha]

Hi Aladin,

my apologies for the late reply...for simplicity purposes...assume that each row of data in my original RAW DATA table is a sale. merci

 

[Aladin Akyurek]

Hi Aladin,

my apologies for the late reply...for simplicity purposes...assume that each row of data in my original RAW DATA table is a sale. merci

Care to post the expected results that go with "Raw Data" you posted?

 

[silentbuddha]

Hi Aladin[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]rate 0
[/TD]
[TD="align: center"]rate 10
[/TD]
[TD="align: center"]rate 20
[/TD]
[TD="align: center"]rate 30
[/TD]
[/TR]
[TR]
[TD="align: center"]1 sale
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]2 sales
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]emp_1
--------
total count of employees = 1
[/TD]
[TD="align: center"]emp_1
emp_2
--------
total count of employees = 2
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]3 sales
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]emp_3
--------
total count of employees = 1
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Aladin Akyurek]

Hi Aladin[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD="align: center"][/TD]
[TD="align: center"]rate 0
[/TD]
[TD="align: center"]rate 10
[/TD]
[TD="align: center"]rate 20
[/TD]
[TD="align: center"]rate 30
[/TD]
[/TR]
[TR]
[TD="align: center"]1 sale
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2 sales
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]emp_1
--------
total count of employees = 1
[/TD]
[TD="align: center"]emp_1
emp_2
--------
total count of employees = 2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3 sales
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]emp_3
--------
total count of employees = 1
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</TBODY>[/TABLE]

Thanks

How do you obtain 2 for 2 sales x rate 20?

 

[silentbuddha]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]rate 0
[/TD]
[TD]rate 10
[/TD]
[TD]rate 20
[/TD]
[TD]rate 30
[/TD]
[/TR]
[TR]
[TD]1 sales
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]emp_2
--------
total count of employees = 1
[/TD]
[/TR]
[TR]
[TD]2 sales
[/TD]
[TD]0
[/TD]
[TD]emp_1
--------
total count of employees = 1
[/TD]
[TD]emp_1
--------
total count of employees = 1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]3 sales
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]emp_3
--------
total count of employees = 1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi Aladin,

my aplogies, the above table shold be the output I am hoping to achieve...thanks