topic Re: How to use Count and Distinct over period? in QlikView

How to use Count and Distinct over period?

Anonymous — Mon, 26 Jan 2026 16:26:21 GMT

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?

Re: How to use Count and Distinct over period?

Gysbert_Wassenaar — Fri, 06 Jun 2014 11:56:38 GMT

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.

Re: How to use Count and Distinct over period?

Anonymous — Fri, 06 Jun 2014 12:43:25 GMT

I am sorry.

I want the answer to be 4 not 5

Re: How to use Count and Distinct over period?

Anonymous — Fri, 06 Jun 2014 12:52:56 GMT

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.

Re: How to use Count and Distinct over period?

sarvjeet — Fri, 06 Jun 2014 13:06:11 GMT

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])

Re: How to use Count and Distinct over period?

Anonymous — Fri, 06 Jun 2014 14:00:18 GMT

ok, Sarvjeet Singh

what does that expression do:

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

What is QlikView doing?

Re: How to use Count and Distinct over period?

sarvjeet — Fri, 06 Jun 2014 14:06:01 GMT

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

Re: How to use Count and Distinct over period?

Anonymous — Fri, 06 Jun 2014 14:39:55 GMT

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

Re: How to use Count and Distinct over period?

sarvjeet — Fri, 06 Jun 2014 14:56:29 GMT

If Container is null use expression :

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