Help with summary formula!!

Snigdha Verma · May 2018

Hello!

I need to add a summary by Campaign, date range and gift types and for some reason I am not getting any errors with my formula but the result is $0.

if {GfCmps_1.GfCmps_1_Description} = "XYZ" and {@This FY}= True and ({Gf.Gf_Type} in

["Pledge","Stock/Property (Sold)","MG Pay-Cash", "Planned Gift", "Recurring Gift Pay-Cash"] or {Gf.Gf_Type} in ["Cash"] and {Gf.Gf_GftsLnkdtPlnndGft} = No) then {GfCmps_1.GfCmps_1_Amount}

Chester Bammel · May 2018

I think the issue is likely due to the OR in your formula. It is putting the OR condition in the wrong place. You need to add parenthesis where you want the or to be.

Is it: or ({Gf.Gf_Type} in ["Cash"] and {Gf.Gf_GftsLnkdtPlnndGft)

or is it: ({Gf.Gf_Type} in

["Pledge","Stock/Property (Sold)","MG Pay-Cash", "Planned Gift", "Recurring Gift Pay-Cash"] or {Gf.Gf_Type} in ["Cash"] )

or is it: ({Gf.Gf_Type} in

["Pledge","Stock/Property (Sold)","MG Pay-Cash", "Planned Gift", "Recurring Gift Pay-Cash"] or ({Gf.Gf_Type} and {Gf.Gf_GftsLnkdtPlnndGft))

It's all different. Place your () correctly and you will likely be fine.

Snigdha Verma · May 2018

Chester, that fixed it! Thank you!

Snigdha Verma · May 2018

A followup question:

When I use the same formula and remove the FY portion I should get the same number as with FY (looking at same dates thus far) but that is not matching.

Below are the two formulas:

THIS FY:

{Gf.Gf_Date} in [CDate (2014, 07, 01), CDate (2018, 06, 30)]

TOTALS:

if {GfCmps_1.GfCmps_1_Description} = "XYZ" and (({Gf.Gf_Type} = "Cash" and {Gf.Gf_GftsLnkdtPlnndGft} = No) or

{Gf.Gf_Type} in ["Pledge", "Stock/Property (Sold)", "MG Pay-Cash", "Planned Gift", "Recurring Gift Pay-Cash"])

then {GfCmps_1.GfCmps_1_Amount}

TOTALS BY FY:

if {GfCmps_1.GfCmps_1_Description} = "XYZ" and {@This FY}= True and {Gf.Gf_Type}in

["Pledge","Stock/Property (Sold)","MG Pay-Cash", "Planned Gift", "Recurring Gift Pay-Cash"] or ({Gf.Gf_Type} = "Cash" and {Gf.Gf_GftsLnkdtPlnndGft} = No) then {GfCmps_1.GfCmps_1_Amount}

Chester Bammel · May 2018

It's kind of hard to tell by just looking at this, especially since your two formulas have the OR in reverse sequence.

But, I'd have to say you are excluding a portion of the records in some way, and this is why your totals are different.

I believe there is a typo. Is it in the text you sent me or is the typo actually in your formula?

You are saying {Gf.Gf_Date} in [CDate (2014, 07, 01), CDate (2018, 06, 30)] Shouldn't that first date be (2017,07,01) and not 2014? If it is that way in your report, that may be the problem. If the report formula is correct then my best guess would be that without the FY included, it is including dates outside of that date range. I wish I could be of more help.

Snigdha Verma · May 2018

Thanks Chester.

Actually, the 2014 is intentional as there are a handful of gifts that need to be included here.

Could you clarify what you mean by OR in the reverse sequence?

I think there are too many conditions. Until I add the This FY, everything is working. That seems to be throwing it off.

Chester Bammel · May 2018

In the TOTALS formula, (({Gf.Gf_Type} = "Cash" and {Gf.Gf_GftsLnkdtPlnndGft} = No) comes at the front of the formula. in the TOTALS BY FY formula it comes near the end of the formula. That threw me off a bit when I first looked at it, but I could not catch any differences.

You might try copying and pasting the exact identical formula in both places and just inserting the FY into TOTALS BY FY formula. But you would need some way to verify the answer was correct - even if they matched.

Sorry, I wish I was more help, but obviously I'm at a disadvantage not having the data or the report in front of me.

Snigdha Verma · May 2018

Yes, I have tried using the same formula and inserting the FY criteria but it keeps telling me a Boolean is required.

Thanks anyway!

Chester Bammel · May 2018

Sorry for the delayed response. It’s been several days of meetings.

This is tough, not having the report or the data to look at. The only thing that the boolean would be in reference to would be the date range, I believe. So you could try turning into a boolean of sorts.

Before that though, in this case could the date range be a separate AND statement outside of this IF? In other words, you could have this entire IF formula in parentheses and follow that with

AND ({Gf.Gf_Date} in [CDate (2014, 07, 01), CDate (2018, 06, 30)])

If that won't work and the date range really does need to be inside the IF statement, then try creating a separate formula like this: (Name the formula something like "DateRange")

if {Gf.Gf_Date} in [CDate (2014, 07, 01), CDate (2018, 06, 30)] then 1 else 0

Then you could have your main formula say something like:

if {GfCmps_1.GfCmps_1_Description} = "XYZ" and ({DateRange}= 1) and {Gf.Gf_Type}in

["Pledge","Stock/Property (Sold)","MG Pay-Cash", "Planned Gift", "Recurring Gift Pay-Cash"] or ({Gf.Gf_Type} = "Cash" and {Gf.Gf_GftsLnkdtPlnndGft} = No) then {GfCmps_1.GfCmps_1_Amount}

Help with summary formula!!

Comments

Categories