Formula help needed for Crystal Report

Chris Z

I'm having trouble with the logic for a formula involving a date, and wondering if anyone has a solution.


My situation is that we operate on a cash basis, with a fiscal year that starts on Sept 1.


I have created a solicitor report which shows everyone in a portfolio.  My export pulls all gifts for constituents with relationship solicitor, with soft credits going to both, and no pledges.  I have columns showing the current FY and last FY.  I'm using a formula for each total, not summary fields from RE.


So for the 2019 FY, I want any cash gifts to the 2019 Annual fund, or any gift date >= 9/1/18 with a gift type of pay-cash.


One problem exists where a payment on last year's pledge will be included with last year's total, even if it was made after the date of 9/1/18.  I don't want these payments in to show in my 2018 Annual total after they are paid.


Here's the formula I'm using:

IF {CnGf_1.CnGf_1_Fund} in ["2019 Annual Giving", "2019 Annual Giving Online"] THEN {CnGf_1.CnGf_1_Amount} ELSE

IF ({CnGf_1.CnGf_1_Date} >= DATE(9/1/2018) AND {CnGf_1.CnGf_1_Fund} in ["2018 Annual Giving", "2018 Annual Giving Online"] AND {CnGf_1.CnGf_1_Type}="Pay-Cash") THEN {CnGf_1.CnGf_1_Amount}


What appears to be happening on the second line is it includes the gifts if there is ANY date after 9/1/18, the annual amount is included.  So my 2018 payments are counted twice.  It is like the AND isn't working.


I've tried using the date format of DATE(2018,9,1), but that didn't have any positive effect.


Any suggestions?  If it is easier to email me, you can contact zello@mpm.edu


Thanks in advance,

Chris

Joshua Bekerman bCLO bCRE

Hi Chris, give this a try:


IF  ({CnGf_1.CnGf_1_Fund} in ["2019 Annual Giving", "2019 Annual Giving Online"]) OR 

    (

     ({CnGf_1.CnGf_1_Date} >= DATETIME(2018,9,1,0,0,0))

     AND ({CnGf_1.CnGf_1_Fund} in ["2018 Annual Giving", "2018 Annual Giving Online"])

     AND ({CnGf_1.CnGf_1_Type}="Pay-Cash") 

     )

THEN {CnGf_1.CnGf_1_Amount}


 

Denise Covington

Ditto what Joshua said.  Also, you may want to consider gift type cash for the first part.  And any other gift types you need to include.  We use Other for payroll deduction, but they are like cash gifts. 

Chris Z

Thank you.  That worked great.


Now, next question.  How would I do this for one year earlier, where the gift dates are BETWEEN 9/1/17 and 8/30/18?

Would the format be:

IF {CnGf_1.CnGf_1_Fund} in ["2018 Annual Giving", "2018 Annual Giving Online"]

OR  (

     ({CnGf_1.CnGf_1_Date} >= DATETIME(2017,9,1,0,0,0) AND {CnGf_1.CnGf_1_Date} <=DATETIME(2018,8,31,23,59,59))

     AND ({CnGf_1.CnGf_1_Fund} in ["2017 Annual Giving", "2017 Annual Giving Online"])

     AND ({CnGf_1.CnGf_1_Type}="Pay-Cash")

     )

THEN {CnGf_1.CnGf_1_Amount}


And to Denise's point, this might be why I need the Gift Type in the first part, so it doesn't count twice.


Again, thank you!

Chris

Joshua Bekerman bCLO bCRE

The date between line should be:


({CnGf_1.CnGf_1_Date} in DATETIME(2017,9,1,0,0,0) to DATETIME(2018,8,31,0,0,0)


I think all date fields export from RE with the same 12:00:00 AM  so there shouldn't be a need for the "23,59,59"