How to count the number of occurences of a field based on selection

[Anonymous]

Hi,

I would like to count the number of occurences of a particular field , based on the context of the selection being made.

For example, I have salesmen who can sell Oranges or Apples. And when I select Orange as the product, I need the num of occurences to only reflect the count of Salesman based on the product.

Product	Salesman	Num of Occurences
Orange	Richard	3
Orange	Richard	3
Orange	Daniel	2
Orange	Richard	3
Orange	Leon	1
Orange	Cindy	1
Orange	Daniel	2

If I choose Apple, then I'll need the num of occurences for Salesman to change based on Apple as the product .

Product	Salesman	Num of Occurences
Apple	Richard	1
Apple	Leon	2
Apple	Daniel	1
Apple	Leon	2

This seems somewhat like a trivial problem but I still couldn't get it to work with Count(DISTINCT Salesman) as I suppose some set analysis or aggr () function might be needed...

Can someone kindly pls provide a solution?

Thanks!

[pokassov]

Hi!

sum(aggr(sum( distinct [Num of Occurences]),Salesman))

[Anonymous]

Hi,

Thanks for your speedy reply!

The "Num of Occurences" is the field which I would like the expression to be...

So I think if I tweak your suggestion, the expression might be...

sum(aggr(count( distinct Salesman),Salesman))

Product	Salesman	Num of Occurences
Apple	Richard	1
Apple	Leon	1
Apple	Daniel	1
Apple	Leon	0

Interestingly, it did set the the duplicate entry to "0" ...which is not quite something I need at this pt, but rather useful...

Do you know how to fix this so that it would get something my expected result in my 1st post?
Many thanks once again!!!

[Anonymous]

Hi,

Thanks for your speedy reply!

The "Num of Occurences" is the field which I would like the expression to be...

So I think if I tweak your suggestion, the expression might be...

sum(aggr(count( distinct Salesman),Salesman))

Product	Salesman	Num of Occurences
Apple	Richard	1
Apple	Leon	1
Apple	Daniel	1
Apple	Leon	0

Interestingly, it did set the the duplicate entry to "0" ...which is not quite something I need at this pt, but rather useful...

Do you know how to fix this so that it would get something my expected result in my 1st post?
Many thanks once again!!!

[#5]

Hi William,

you just want the count of salesman then, rather than a distinct count? Unless I am missing something.

See attached

hope that helps

Joe

[Anonymous]

Hi Joe,

I'm not sure if you've attached something, but yes, I am not looking for a distinct count.

The reason why I need to a count of the salesman, is because I have a fixed operating cost per unique salesman, and my logic distributes the fixed operating cost over the count of salesman in a table structure.

I did some tweaking from the earlier post and found that this worked for me

=FixedOH * sum(aggr(count( distinct Salesman),Salesman))

so I could give a cost if it isn't already in the table.

The sum () formulae would return 1 if it's distinct in my selection, and 0 if it's non-distinct, so it worked.

Thanks for your response anyway!

[pokassov]

I think Salesman Leon somelike different from second "Leon".

Instead if you select Product and Salesman as dimension and count(Salesman) as expression, you will have to see only one "Leon" in your chart.

[Anonymous]

Hi Sergey,

Thanks for your help... This worked for my logic

=FixedOH * sum(aggr(count( distinct Salesman),Salesman))

where FixedOH is a fixed cost I need to give to only one salesman within the current selection