8 Replies Latest reply: Jun 6, 2014 10:56 AM by sarvjeet singh

# How to use Count and Distinct over period?

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?

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?

I am sorry.

I want the answer to be 4 not 5

• ###### Re: How to use Count and Distinct over period?

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?

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?

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?

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?

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?

If Container is null use expression :

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