How to get the right total when using 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 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?

[#2]

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

More info on aggr: http://community.qlik.com/thread/38796

[Anonymous]

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

[#4]

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)

[Anonymous]

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

[hic · Accepted solution]

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

[jvitantonio]

Hi Darri,

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

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

Best,

JV