I'm trying to assist Development with including last year's gift amount in an Output Set along with Solicitation Goal and various other fields. The Contribution_last_year_giving appears to only return the last gift - not the total for last year giving. What gives? Is there an object that give the cumulative amount of gifts?
Todd,
Here’s what we use for Year To Date giving – you can use this as a starting point:
Description data_select data_from data_where
YTD Gifts sum(isnull(cont_amt, 0)) vs_contribution cont_type = 'G' and cont_dt > cast(cast(year(getdate()) as char(4)) + '-01-01 00:00:00' as datetime)
YTD Pledged sum(isnull(cont_amt, 0)) vs_contribution cont_type = 'P' and cont_dt > cast(cast(year(getdate()) as char(4)) + '-01-01 00:00:00' as datetime)
YTD Giving sum(isnull(cont_amt, 0)) vs_contribution cont_dt > cast(cast(year(getdate()) as char(4)) + '-01-01 00:00:00' as datetime)
-steve carlock
Information Technology Manager
The Granada
(805) 899-3000 x 111 (phone)
(805) 899-3081 (fax)
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Todd Bezold Sent: Thursday, April 15, 2010 11:06 AM To: Steve Carlock Subject: [Tessitura Development Forum] Contribution_last_year_giving
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi Todd!
This parameter uses the follow where clause, which could be why you are feeling thrown off: cont_dt > dateadd(yy, -1, getdate())
For the non SQL types, this translates to any contribution made within one year of yesterday. So, if you are looking for the sum of giving to all of last year, you probably are not getting it - you are getting a sum of all giving in the past 365 days.
Steve offers some great suggestions!
Addtionally, you may want to look into [TR] Query Parameters, which would allow you to specify a date range or campaigns contributions had been made towards.
Nick
I recently undertook some RFM analysis for which I wanted to know donations over the last 12 months, where the last 12 months was a changing parameter depending on when I was running the reports.
Our lovely IT guy sorted out some special RFM parameters for me in Output Set Builder which calculated this and allowed me to pull the data I needed on a regular basis and it automatically upated it.
I don't have the code as I'm a lowly non-tech person but if you contact Paul Rolfe at The Sage Gateshead I'm sure he'd be happy to pass some information on to you.
I was very frustrated that this information wasn't easily accessible so hopefully it'll be something that comes up in future... or in the next gen!
Bw, Alison
In conjunction with the Lean Mean Fundraising webinar, the network posted some sample output elements for FRV (3 month, 1 year, 3 year, and lifetime) along with some other helpful analytic values.
You can access both the recorded webinar and the sample output elements (click on "sample query") here.
Hope these can be beneficial to you!