Hi all,
I’m in the process of building a pivot table where I am hoping to see constituents with a specific constituency code along with the amount they have given within our fiscal year. I am hoping to see all constituents even if they have not donated yet. Does anyone have any recommendations for a value formula that I can use? I’m assuming that I would use an IF statement but have yet to figure out the best way to phrase it. Is there a way to say I want to see the gift amount within a given year/campaign and if not then show 0/blank/null?
Thanks in advance!
One of my favorite formulas (credit goes to this forum, where I got it initially!) is this:
IF ( ( [Total Amount] , [Offset -1] , [Fund NOT mem | 1.11.22] ) > 0 AND ISNULL ( ( [Total Amount] , [offset 0] , [Fund NOT mem | 1.11.22] ) ) , ( [Total Amount] , [offset = -1] , [Fund NOT mem | 1.11.22] ) , NULL )
Where 'Offset' refers to 'Fiscal Year Offset', set to -1 , or 0.
Essentially, this formula is asking "If someone gave last year (not to membership), AND hasn't given this year (not to membership), show me their giving last year (not to membership)". I love it. This yields this for us:
You could modify this to add in 'IF (constituency = X, Y, or Z), AND ....' to get only those folks you want. Or, you could add a filter on the widget itself to only look at those folks, and you wouldn't have to adjust the formula.