6 Replies Latest reply: Jun 6, 2014 11:55 AM by Juan Vitantonio

# How to get the right total when using 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 because I used the same container many times.

I want the total numbers of container in 2013 to be 4 because I used the same container 4 times in 2013. But I always get the same total number of containers in 2013 which is 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. And I do not want use the Count (DISTINCT [Job Ref])

Does anybody have any idea?

• ###### Re: How to get the right total when using DISTINCT over period?

I think you are looking to group by the Job Ref field to get the number your after. Aggr should work in this case:

aggr(Count (DISTINCT [Container Number]), [Job Ref])

• ###### Re: How to get the right total when using DISTINCT over period?

this is not what I am looking for exactly.

I am only looking for some expression that can help find the total number when I

have the same container number over a period.

When I use DISTINCT in an expression and have same container number over a period the total number uses the DISTINCT function and in the example I get total numbers of container is 1 but the container was used 4 times not once

• ###### Re: How to get the right total when using DISTINCT over period?

I didn't see that your period was dependent on month. The same expression should work, just cut by month...or even year and month if you need to when you have more than one year's worth of data.

aggr(Count (DISTINCT [Container Number]), Month)

aggr(Count (DISTINCT [Container Number]), Year, Month)

• ###### Re: How to get the right total when using DISTINCT over period?

Hi, Kris

It did not work the aggr function

You put the data and QV example to show you.

I can see the total numbers for both the function you talked about were 1

• ###### Re: How to get the right total when using DISTINCT over period?

Hi Darri,

You need to add a sum() to the expression:

sum(aggr(Count (DISTINCT [Container Number]), Year, Month))

Best,

JV

• ###### Re: How to get the right total when using DISTINCT over period?

If I understand you correctly, you want to count not the number of containers, but the number of combinations of containers and job references? I.e. If the same container has been used on 4 jobs, it should be counted four times.

If so, you should use

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

You can probably do it with Aggr() too, but then you need to wrap Aggr() in an aggregation function, e.g.

Sum(Aggr(Count (DISTINCT [Container Number]), [Job Ref] ))

HIC