How to use Count and Distinct over period?

[Anonymous]

Hi, QV community

I have little problem that needs solution.

I have this data and I want to count numbers of Containers

Job Ref	Container Number	Description	Year	Month
REYE507	SANU421	Fish	2013	may
REYE507	SANU421	Meat	2013	may
REYE604	SANU421	Car	2013	april
REYE604	SANU421	Food	2013	april
REYE610	SANU421	Frozen fish	2013	jan
REYE610	SANU421	Meat	2013	jan
REYE612	SANU421	Car	2013	mar
REYE612	SANU421	Food	2013	mar

as you can see, in this data there is two lines for every Job Ref.

So I use the expression Count (DISTINCT [Container Number]) so that QV don't double counts the containers in every Job Ref.

I get the right numbers of containers in the Job Ref which is 1.

But the problem is when want to know how many containers where in 2013.

I want the numbers of container in 2013 to be 5 but I get 1.

I now that is because I use DISTINCT. I only now that if I use the sow of Rows I get the right answer.

I want to have Pivot table so I have to change the expression.

Does anybody have any idea?

[Gysbert_Wassenaar]

You don't want the distinct count of containers, because there is only one distinct container in 2013, i.e. SANU421. If you think the answer should be four then you want the distinct count of Job Ref. If you want the answer to be five, then you'll first have to explain to me why it should be five.

[Anonymous]

I am sorry.

I want the answer to be 4 not 5

[Anonymous]

ok, I see where you are going with this regarding using distinct count of Job Ref.

But I don't want to use the distinct count of Job Ref

I know you can use distinct count of Job Ref in example I showed you but I have more complex data.

I only want to focus on expression regarding count of containers because sometimes quantity of job ref is not same quantity as quantity of containers so I can't use distinct count of Job Ref.

[sarvjeet]

there are two ways to handle the scenario:

A) Concatinate the Job ref and Container number in scripts and apply hashcode . This will give uniqe number to each job and container.

In chart  use expression Count (distinct <<generated unique number>>)

B)  use expression in chart;

Count( Distinct [Job Ref]&[Container Number])

[Anonymous]

ok, Sarvjeet Singh

what does that expression do:

Count( Distinct [Job Ref]&[Container Number])

What is QlikView doing?

[sarvjeet · Accepted solution]

Qlik will do count on unique combination of Job and Container.

It will also cover scenario below:

Job Ref	Container Number	Description	Year	Month
REYE507	SANU421	Fish	2013	may
REYE507	SANU422	Meat	2013	may
REYE604	SANU421	Car	2013	april
REYE604	SANU423	Food	2013	april
REYE610	SANU421	Frozen fish	2013	jan
REYE610	SANU421	Meat	2013	jan
REYE612	SANU421	Car	2013	mar
REYE612	SANU421	Food	2013	mar

Count(Distinct JobRef ) will give you Count 4

Count (Distinct JobRef&Container) will give count 6

[Anonymous]

Hi, Sarvjeet Singh

I have one more question regarding this data below. I have the same data as you had before but in one Job Ref I have no value in the container number. In my complex data I have a couple times this kind of situation were there is no container number. If I use your expression I am in trouble. Any ideas

Job Ref	Container Number	Description	Year	Month
REYE507	SANU421	Fish	2013	may
REYE507	SANU422	Meat	2013	may
REYE604	-	Car	2013	april
REYE604	SANU423	Food	2013	april
REYE610	SANU421	Frozen fish	2013	jan
REYE610	SANU421	Meat	2013	jan
REYE612	SANU421	Car	2013	mar
REYE612	SANU421	Food	2013	mar

[sarvjeet]

If Container is null use expression :

Count( Distinct{<[Container Number]={'*'}>} [Job Ref]&[Container Number])