Quote:
Originally Posted by Melissa Siobhan 
This is the code for {@Projected FY2008} (fields are from the proposal tab)
If {CnPrProp_1.CnPrProp_1_Status} in
("01 Suspect,02 Prospect,03 Strategy,04 Cultivation,05 Ready,06 Considering") then
(If IsNull({CnPrProp_1.CnPrProp_1_Date_Funded})
then
(If IsNull({CnPrProp_1.CnPrProp_1_Date_Expected})
then 0
else
(if {CnPrProp_1.CnPrProp_1_Date_Expected}
in CDate(2007, 07, 01) to CDate(2008, 06, 30)
then {@Probable Amount}
else
if {CnPrProp_1.CnPrProp_1_Date_Expected}
< CDate(2007, 07, 01)
then 0
else
if {CnPrProp_1.CnPrProp_1_Date_Expected}
> CDate(2008, 06, 30)
then 0))
else 0)
else 0
I know you didn't ask about this, but you don't really need to account for every single possible combination of values. If your record doesn't meet any of the criteria, it will produce a zero, so you don't need to specify those cases. In some cases, a Null value can be treated as being equivalent to a zero, so you may not even need to worry about cases that would produce a Null value. You can also use AND and OR to make complex logical statements instead of looking at every field independently. You only have one case where you need to produce something different from Null or zero, so you can greatly simplify this to only consider that one situation.
Code:
If {CnPrProp_1.CnPrProp_1_Status} in
["01 Suspect","02 Prospect","03 Strategy",
"04 Cultivation","05 Ready","06 Considering"]
AND IsNull({CnPrProp_1.CnPrProp_1_Date_Funded})
AND {CnPrProp_1.CnPrProp_1_Date_Expected} in CDate(2007, 07, 01) to CDate(2008, 06, 30)
then {@Probable Amount} If the CnPrProp_1_Status is Null, it will produce a Null result which is fine. If it's not in your list, then it will produce a zero result, which is also fine.
If the CnPrProp_1_Date_Funded is not Null, it will produce a zero result, which is fine.
If the CnPrProp_1_Date_Expected is Null, it will produce a Null result, which is fine. If it's not in your current fiscal year, it will produce a zero amount, which is fine.
Tying two threads together, you may want to use a formula similar to the {@Gift FY} to calculate the {@Date Funded FY} and compare that to the {@Current FY}, instead of hard-coding the dates for this FY.
Now, to finally get to your original question, it depends on whether you are using Crystal 8.5 or Crystal XI. In both, you can use a running total and the "Use Function" for the evaluation (tying in yet another thread) to create a "distinct count" of donors where the {@Projected FY2008} > 0.
There is another--better--option in Crystal XI. First create a formula {@Null String}
Code:
//leave this section blank
That's right!! Do not enter anything in the formula. Then create a second formula
Code:
If {@Projected FY2008} > 0 Then {Constituent ID field} Else {@Null String} If {@Projected FY2008} is Null or zero, this will produce a Null, which is exactly what you want, otherwise it will output the Constituent ID. If you use the Constituent ID, you can use the {@Null String} as is, because the Constituent ID is a string field, but if you use the System Record ID or some other numeric ID, you'll need to convert the {@Null String} to a number.
Once you have this second formula, you can do a distinct count on that formula.
I know that was a LOT of information. I hope you're not overwhelmed by it.
Drew
Linear Mode
