Possible Set Analysis Question

[jcampbell474]

I am using QV11 and having trouble with an expression.

My issue is:  I have a table loaded with Dept, subDept, product_code, empid, and emp_name.  I need to get a headcount of each employee in the main department.  When I create the chart (straight table), I create these dimensions and use count(distinct(empid)), it appears that QV is counting the distinct empid within each subdept and product_code - effecitvely counting duplicate empids.  The output has to include the subdept, product_code, and headcount fields. 

My output right now is inflated due to employees listed multiple times (they can be in multiple subdepts)…I just need to count the distinct number of empid’s w/in the main department.  I need the expression to ignore subdept and product_code.  I feel like some type of set analysis applies here, but not sure what to do.  Does anyone have any advice/suggestions?

Thank you in advance.

[awhitfield]

Hi Jason,

can you post some example data for this, say in excel?

Cheers

Andy

[jcampbell474]

I can't find a way to attach a file, so here is a sample table:

Dept ID	Product Code	empid	EMPNAME
5009005001	8000	1234567	xxx, yyyy
5009005001	8000	1234568	xx,yyy
5009005466	8000	1234569	x, y
4254644333	3500	2345678	xxxx, xxxx
4254924224	3500	2345678	xxxx,xxxx
4254644222	3500	3456789	xxx,xxx
4254644333	3650	3456789	xxx,xxx
In QV, this gives me a headcount of 7.
It's actually 5 employees, by counting distinct empids.

[#4]

Hi Jason,

you said "The output has to include the subdept, product_code, and headcount fields"

In this case, the answer cannot be different: if there's more than a combination of empid + Product Code + Dept ID you'll see all of them.

If on that table you only want to see once each empid you must avoid other dimensions.

Cheers

Luca

[luciancotea]

... and the definition of main department is...?

[sunny_talwar]

Try this:

=Count(DISTINCT empid)

This will only count the distinct occurrences of each Employee id.

HTH

Best,

Sunny

[#7]

What Luca mentioned is correct.

You can do it with one dimension but not multiple dimensions.

Regards

Koushik Vutha

[jcampbell474]

I was thinking set analysis could manipulate the expression independent of the dimensions.  At any rate, a big thanks to all who replied.  I figured it out.  The datasource contained additional departments that was defined before.  Their numerical range happened to fall w/in a range of the departments I was testing.  So, it's all good now.  Thanks again.

[awhitfield]

PS - if you want to attached something to a post, then click on 'Used advanced Editor' and away you go!

Andy