Hello all. I've been picking this apart for the last few weeks, working with our in house CRM team and we are all a bit stumped and hoping for some help.
We have a number of custom fields in analytics as we count our fundraising income via recognition dates rather than contribution dates, but this all still sits within the contributions cube. I am trying to create a widget showing all income to be recognised this FY but want to exclude any GIK less than £25k. We mark Gift in Kinds in 2 ways, via the Payment Method as well as the Designation.
To get how much recognised in this FY we have to use the below formula, which works exactly as intended (This simply sums the contribution amounts with a recognition date in this FY up to today + with a recognition date between tomorrow and the end of this financial year).
I thought it would be as simple as then expanding it to something like the below where I am minusing off any already recognised gifts (filtered to less than £25k and with a payment method of GIK) + minusing off any expected to recognise gifts this FY (filtered to less than £25k and with a payment method of GIK).
However, all we get are the error message "Error in function definition (Sum): Expecting parameter of type 'Numeric Expression' but found 'Set'.
Changing it away from SUM to Total removes the error but we then can't filter on amount. We've also tried simplifying it to just remove recognised YTD with the same filters to no luck, moving brackets around etc... we also tried doing the same type of formula on more standard fields in analytics such as contribution amount and designation in case it was something to do with our custom fields but we get the same issue when using SUM.
Any ideas on how to get around this would be greatly appreciated!
Lauren
Hello Lauren,
Looks like your formula is using a grouping of "[Recognised YTD1]." My guess it that is where the error is. I'm assuming that you meant to use it as filter (>25k). The format for the sum function is Sum([group by field], <aggregation>). Therefore, the [Recognised YTD1] is being used as a group by field.
Generally, I like to use a pivot table that correctly computes the various parts of the things I wan to add or subtract. Once I know the components work, I then put them into one big formula.
To use the [Recognized YTD1] as a filter, you need to use the following format ( SUM(<aggregation>), [Filter] ). The concept is known as a "filtered value."
Here is an example that only adds the number of contributions of > an amount:
Hi Neil, that might have cracked it! It looks like I was close but because I didn't bracket the end of the first field it was being treated as the group by field rather than aggregation! So simple and the one place we didn't move the bracket.
Good to hear Lauren. It is usually something small that trips us up.