topic Re: Aggr() creating slightly incorrect results in App Development

Aggr() creating slightly incorrect results

Wed, 12 Apr 2017 14:46:44 GMT

I am trying to count the number of jobs my company runs in a given year. We use the same job number over multiple days (or weeks), so I only want to count each job number the for the first date it was used. I have successfully done this in the data load script, but I am getting slightly incorrect results using the Aggr() function. Here is what I am doing and the results.

Data Load:

ProductionData:

LOAD JobDate,

Year(JobDate) AS JobYear,

Text(Month(JobDate))&'-'&Text(Year(JobDate)) AS JobMonthYear,

JobNo;

SQL SELECT JobDate,

JobNo

FROM prProdControl

WHERE JobDate >= '2010-01-01';

Counter:

Load

Text(Month(MinJobDate))&'-'&Text(Year(MinJobDate)) AS JobMonthYear,

Count(JobNo) AS JobCount

Group By Text(Month(MinJobDate))&'-'&Text(Year(MinJobDate));

Load

Min(JobDate) AS MinJobDate,

JobNo

Resident ProductionData

Group By JobNo;

Table:

As you can see, the total counts are the same, but there are small differences. I have verified externally that the Sum(JobCount) results are correct. I have tried everything I can think of and I can't find where these variances are coming from. Thanks in advance.

Re: Aggr() creating slightly incorrect results

sunny_talwar — Wed, 12 Apr 2017 15:25:41 GMT

May be try this

Sum(Aggr(If(Min(TOTAL <JobNo> JobYear) = JobYear, 1, 0), JobNo, JobYear))

Re: Aggr() creating slightly incorrect results

Wed, 12 Apr 2017 15:27:48 GMT

Thank you! Could you explain why this worked?

Re: Aggr() creating slightly incorrect results

sunny_talwar — Wed, 12 Apr 2017 15:50:04 GMT

Why this worked? Difficult to say, but how it worked is I compared the Job Year for each JobNo to there Minimum JobYear. When it matched I assigned it 1, when it did not I assigned it 0. Sum all the 1s to get the count.