topic Re: Possible Set Analysis Question in QlikView

Possible Set Analysis Question

jcampbell474 — Thu, 26 Mar 2015 15:55:14 GMT

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.

Re: Possible Set Analysis Question

awhitfield — Thu, 26 Mar 2015 15:58:41 GMT

Hi Jason,

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

Cheers

Andy

Re: Possible Set Analysis Question

jcampbell474 — Thu, 26 Mar 2015 17:16:56 GMT

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.

Re: Possible Set Analysis Question

Thu, 26 Mar 2015 17:32:30 GMT

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

Re: Possible Set Analysis Question

luciancotea — Thu, 26 Mar 2015 17:33:48 GMT

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

Re: Possible Set Analysis Question

sunny_talwar — Thu, 26 Mar 2015 19:18:29 GMT

Try this:

=Count(DISTINCT empid)

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

HTH

Best,

Sunny

Re: Possible Set Analysis Question

Thu, 26 Mar 2015 19:30:20 GMT

What Luca mentioned is correct.

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

Regards

Koushik Vutha

Re: Possible Set Analysis Question

jcampbell474 — Thu, 26 Mar 2015 19:40:38 GMT

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.

Re: Possible Set Analysis Question

awhitfield — Fri, 27 Mar 2015 09:40:24 GMT

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

Andy