Improved Calgary CrossCount Functionality

[mark_frisch]

Calgary CrossCount returns 1 row for every permutation and combination of possible crosscounts for the given crosscount fields.  This idea is to use the additional query criteria to limit the output of rows.

Example:  State Code = 'MI' and Mosaic Type = 'A01'  could be our Criteria.  and State Code, Mosaic Type and Gender would be our crosscount fields.

Calgary crosscount (and append) would output (51 * 71 * 4) 14,484 rows (all states * all mosaic types * all gender values)

The desired crosscount would output (1 * 1 * 4) 4 rows (MI * A01 * 4).

This is a simplified example just to demonstrate my confusion when Alteryx responded to me that the tool is working as designed.  In order to reduce the crosscount output, you must restate your query criteria in a filter post the calgary tool.  If the count of rows output exceeds a thresshold, you can't even use the tool as it will generate too many rows.

By defining criteria in the Calgary Input tool, only the desired output records are generated.  But by defining criteria in the Calgary crosscount tools, only the desired "combinations" have values plus it outputs all permutations and combinations of other non-qualifying records.

Please consider this request for an enhancement.

Thanks,

Mark

[50ld13r]

Shame even now this has not been fixed.  Why the cross count is returning a 0 for a value that you don't want due to the filter is very strange behavior.  As mentioned it also limits its usefulness. 

Another example I had was I wanted a cross count of age distribution bands by UK postcodes that begin NG1.  Total theoretical permutations (6 * 321) = 1,926 actually was only 1,119 with the data.  This is not possible in a cross count as it ignores the where clause of NG1 and tries to pull every postcode (1.6 million * 6 age bands) and fails.  

The order of processing should be filter first then cross tab.  No one is bothered about the 0 values as that's why they are being filtered.