Distinctcount Shows Wrong Result 2

[codrutza]

CR XI

Please help me with this:

I try to make a manual cross tab;

In record selection formula I have {@DepLoc} startswith ["Ag","Bu","Ge","Sh","Vu"]
and {@ArrLoc} startswith ["Fos","Rot","Nor"]

In the manual cross tab I have "Ag","Bu","Ge","Sh","Vu" on horizontal and "Fos","Rot","Nor" on vertical

I have for example for Ag/Fos formula @AgFos: if distinctcount(jobsAgFos)<> then sum(@daysbetweendepartureandarrivalAgFos) /distinctcount(jobsAgFos) else 0

For some locations I have no jobs; why for these appears at distinctcount 1, where it should be 0? And in the same time, why at @AgFos appears distinctcount with 1 jobs more? It ruins my formulas

I must doing something wrong....

 

[kenhamady]

This happens when you do a distinct count of an IF-THEN. You have to make sure you specify a NULL value as your else or the value you use (like empty string) will count as one more value. This link explains in more detail how to get a CR formula to return a null:

http://www.kenhamady.com/form14.shtml

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[codrutza]

Excellent. Thank you very much, Ken.