Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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)
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
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
Hi Darri,
You need to add a sum() to the expression:
sum(aggr(Count (DISTINCT [Container Number]), Year, Month))
Best,
JV