Cross Tab Distinct Count Error

[AyJayEL]

Has anyone else noticed that the Cross Tab Distinct Count doesn't always count distinctly! I have a cross tab with a distinct count on students. The rows are by Special Ed Need and type of school and the columns are the National Curriculm Year (NCY) they are in, with a formula grouping the years from NCY to Early Years (- 3 to -2) Primary (-1 to 6) and Secondary (7 to 15). If I double check the results with a flat file and a manual count I can see that it has counted some records twice (or more). It is not doing a distinct count. If I right click on the field on the cross tab it says that it is a distinct count.

It's got me beat.

Learn something new every day *:->*
AyJayEl

 

[lbass]

You might want to check what's going on by recreating part of the report within Crystal in the details section, i.e., add the student ID field to the details section, and group on Special Ed Need and Type of School. Then add a running total using a distinct count of the student id for each record. You might want to try resetting it on change of one group and then the other.

I'm guessing you might find that students are represented in multiple Special Ed Need groups and their presence would be distinct within each group. If this is true, and you have the Special Ed Need group as the highest order group, you would get duplicates of the school (and student) for each special need. At least doing this might help clarify what's going on.

-LB

 

[synapsevampire]

A spelling error or additional spaces may account for this.

I just tested and I couldn't get the distinct count to fail.

-k

 

[chelseatech]

Remember that distinct count does a complete distinct count over the level selected, so duplicates are combined at each level.

Group:
1,1,1,2,2,2,3,3,3,3,3,1,4,5 => Distinct Count=5
Group:
3,3,3,3,6,7,8,9, => Distinct Count = 5

Grand Total Distinct Count 9 (not 10)

The duplicate '3's are combined for the grand total. In Cr you can solve this with a formula and variables.

In a crosstab you could solve it by creating a formula to combine the Distinct Field and the Group Field and do a distinct count on that.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[AyJayEL]

I have taken the report apart and still can't see what is going wrong! A child only has one need. It is only in one school and is only in one curriculum year.

The count (distinct) in the body of the report is wrong and the total to the right of the row is wrong. The totals in the columns are correct. Like this:- (Cut down version)
--------------------- -2 -1 0 1 2 3 etc Totals
EBD- Special School 2 9 14 15 23 26 89
---- Depart --------- 3 1 5 8 8 14 39
-----Totals ------- 4 6 11 18 30 25 84

The bottom row is correct. The grand total is correct.

I have 3 tables Student, Placement, Needs all connected by equal joins. Starting from Student joined by Student ID to the Needs table and from Needs to the Placement table by the placement id.

I have tried to create a formula to put into the cross tab but can't see it once I have created it.

Sigh,,, it's Monday.

Learn something new every day *:->*
AyJayEl

 

[lbass]

What happens if you concatenate your two row fields and use that formula as one row?

-LB