
Re: How to get the right total when using DISTINCT over period?
Kris Balow Jun 6, 2014 11:06 AM (in response to Darri Hilmarsson)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

Re: How to get the right total when using DISTINCT over period?
Darri Hilmarsson Jun 6, 2014 11:21 AM (in response to Kris Balow )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?
Kris Balow Jun 6, 2014 11:26 AM (in response to Darri Hilmarsson)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?
Darri Hilmarsson Jun 6, 2014 11:36 AM (in response to Kris Balow )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?
Juan Vitantonio Jun 6, 2014 11:55 AM (in response to Darri Hilmarsson)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?
Henric CronstrÃ¶m Jun 6, 2014 11:54 AM (in response to Darri Hilmarsson)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