Why is my aggregation count off by 1 ???

[nbailey]

I have a multi query report in which my lower level query are selecting student ids and passing them back to the high level query where the aggregation is done with count (distinct student id)

The values on the report are off by one in the report ... see enclosure for more details ...

Does anyone know what could be causing this aggregation to be rolling up an extra count  in each of my report cells ???

[Lynn]

You could try specifying the scope using the "for" clause so it is counting just exactly the way you want it to.

count(distinct [StudentID] for [Faculty],[Class])

[nbailey]

Thanks for your reply... but that did not work ... here is the statement I used and got the same results...

count ( distinct [Union1].[amt3] for [Union1].[CRN_KEY], [Union1].[DBNAME] )

[Lynn]

Did you look at the tabular data coming back from each of the three queries to be sure there isn't something unexpected in the data itself that is causing your problem?

I'm not sure what else to suggest other than basic troubleshooting techniques.

[nbailey]

When I run one of the sub query's as a stand alone query at the top level (1st level)... I get the correct results.  It is when I drop them down 1 level to the 2nd level that I get the counts being incremented by one ...

[jmills]

What you could try doing is set the aggregation to none in the lower queries and turn each one back on between report runs to see what query is causing you troubles.  You could also try setting the Solve Order to ensure that the roll up is sequenced, but use this as a last resort.

[nbailey]

Thanks again for replying so quickly...
in my attachment you will notice that all of the aggregation is done in the High level query with the aggregation functions set to Automatic and automatic.   In the low level query I am collecting the student Id's  in the 3 amt fields and the aggregation at those lower levels is set to NONE and NONE.  Maybe this in not correct ???
I have tried aggregating the counts at the lower level and then passing the results to the higher level but have gotten bizarre results... so I went back to the 1st way. 

[Lynn]

I think I might have the answer....you are doing a count, not a sum. What are the possible values for amt3 in each of the queries? Does one query return the actual value while the other two just have zero or some other dummy value? If so, you are counting the zero values and distinct would add one to your correct total.

If I'm right then replace the zero with a null. The count function will not count nulls.

[nbailey]

That is exactly what I am doing ... passing 0's in the amt fields that are not being populated ...

At the risk of sounding really dumb... how do I return nulls ?   
I have tried ( is null, null,  nulls and '') but cognos does not like it ?   

should I keep the aggregation at those lower level queries as None and None ? 

[Lynn]

Another way to skin the cat is just subtract 1 from the expression in the union query. Everything is working otherwise and you know the reason for it, so maybe this is the easiest way to go.

Code Select Expand


count ( distinct [Union1].[amt3] for [Union1].[CRN_KEY], [Union1].[DBNAME] ) - 1


Not sure what the issue is with the null so I don't think you are the only numb one :)

I would think null or '' would work, but hard to know what is happening in your case. It may vary depending on which database you have. If the ID is numeric then the null string '' might cause an error for mismatched data type.

[nbailey]

Thanks Lynn, 
you have been a huge help with this...
I am very close now.  I could not use the -1 idea as not all of the counts were off by 1.  looking at my example in the enclosure again the 13 and the 3 in the amt1 for 201210 column were the correct values.   Very strange.   

I changed the report to do the aggregation in lower level queries using count( distinct [PIDM_KEY] for [CRN_KEY], [DBNAME])
and aggregation  function = none and rollup aggregation function = none.   And then in the high level query I set the expression to [Union1].[amt1]  with aggregation  function = TOTAL and rollup aggregation function = AUTOMATIC. 
I have a similar report that was doing it this way and works fine.
But the problem I am having now is that the results are multiplied by itself ( 19 is 361,  17 is 289, 3 is 9 etc).
I can get the correct results in the report if I use [Union1].[amt1]/[Union1].[amt1] in the expression of the high level query. 

do you have any suggestions as to how I can get the correct results without doing the extra divide   ???

Thanks again

Norm
   

[CognosPaul]

Taking a step back. Why are you even trying to do a count? Why not put the a field with the expression 1 in all three of the original queries and sum on that? or something similar: if amt1 <> 0 then 1 else null

Alternatively, you could try doing something like count(distinct (if (amt1<>0) then (amt1))

[Lynn]

If you are collecting student ID numbers and counting them you'd need to change that logic to get counts in the lower level queries and then sum in order to follow Paul's advice on using a sum.

I would generally set the aggregation type to NONE when using an explicit summary function with a scope clause. You are already telling it exactly how to aggregate so the answer it returns is the answer you want without further aggregation. That might explain why you see the value multiplied by itself. Otherwise, try to remove the for clause of the calculation and leave the aggregation setting as total.

As for some data correct and some off by 1...it all depends on what the individual queries are returning. If some situations don't return a row with the dummy 0 value then you'd get correct ID counts.

[nbailey]

Got it ... thanks so much ... ;D
your suggestion to "try to remove the for clause of the calculation and leave the aggregation setting as total." Worked

Thanks to Paul as well... that was an alternative I did not consider before.   I may play with that some to see if I can get that to work as well ... for future jams like this one   :)

Thank Again