• Replies 1 reply
  • Subscribers 961 subscribers
  • Views 42 views
  • Users 0 members are here
Related

Formula to show renewable gifts from last year in a current year column

John Gerteisen
$organization

Hello, 

I am attempting to show the number of renewable gifts from the previous fiscal year against the total number of gifts that were renewed. I would like to be able to use some formula like:

case
when [MaxFiscal Current Year Offset] = 0
then (sum([Amount1]) , [MaxFiscal Current Year Offset] - 1)
end

This is not working. Can anyone suggest a suitable alternative?

Thank you,

John

  • $organization

    Hi John,

    If this is just an Indicator widget, then let's say the Secondary Value is the count of renewable gifts from last year. Start with a widget or dashboard filter for [Campaign Fiscal Current Year Offset] set to = -1, and then a simple:

    SUM( [Contribution Count] )

    And the number that have been renewed as the Primary Value, I'm going to assume is determined at the constituent level, so...

    SUM( [Constituent ID],
     ( [Total Contribution Count] , [Campaign Fiscal Current Year Offset] )
     )

    Because the widget is limited to constituents with gifts last year, the second formula will count only contributions in the current fiscal year (overriding the -1 filter) from those constituents with gifts in the last fiscal year.