Aggregates in Query

Options
Jamie Avalos
Jamie Avalos ✭
Eighth Anniversary First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
Does anyone know if there is an option in Queries when pulling donations from year to year that has aggregates with a + or - on each year for the totals? We have a new Chief Director and he asked for this infomation for all gifts from 2015,2016 and 2017 YTD. I didn't think Raiser's Edge had this option but just wanted to be sure before I answer?
Tagged:

Comments

  • Melissa S Graves
    Melissa S Graves ✭
    Ninth Anniversary Facilitator 3 Photogenic
    I do not know what you are asking. Can you explain what you mean by + or -?
  • Jamie Avalos
    Jamie Avalos ✭
    Eighth Anniversary First Reply
    Basically if a list is pulled from the querie for all gifts from 2015, 2016, and 2017 year to date...is there an option to have them side by side with donors who have given during those dates and the + shows they have increased over the year or the - sign that they have decreased in giving? 

     
  • Melissa S Graves
    Melissa S Graves ✭
    Ninth Anniversary Facilitator 3 Photogenic
    Have you tried the comparative report? You can only compare 2 timeframes at a time but it shows increases and decreases.
  • Gina Smith 2
    Gina Smith 2 ✭
    Ninth Anniversary Facilitator 1 Photogenic
    You can run the Analyitcal Report, Comparitve Report and specify years to compare.  It gives a variance column in the report.  You can choose to filter only consituents that increased, decreased or show both.


    You could also export your query to excel.  Then use the 3 years of gift totals data to apply a formula for variance from one year to the next.  It would (roughly) look like this:

      2015 2016 Variance % Variance $ 2017 Variance % Variance $  $100.00  $200.00 100%  $100.00 $100.00 -50%  $(100.00)
     
    John Smith
    Formula for Variance % - Divide Year 2 Gift total by Year 1 Gift total and subtract 1.  (=C2/B2-1)

    Formula for Variance $ - Subtract Year 1 Gift Total by year 1 Gift total (=C2-B2)


    Hope this helps.
  • Peter Gulka 3
    Peter Gulka 3 ✭
    Tenth Anniversary Facilitator 2 Photogenic
    Query isn't a reporting tool, which mentally can trip some people up. This kind of thing is a perfect example. It can do some basic sum calculations in isolation, but not the comparatives you describe. If it were me, I would dump the query into Excel and do a quick frmula to create the comparatives, and then add some formatting to make it look pretty.


    If this is something you'll be doing regularly, you might want to use one of the canned reports, or build a custom Crstal Report. That would be relatively straightforward to develop.

  • Jamie Avalos
    Jamie Avalos ✭
    Eighth Anniversary First Reply
    Thank you so much!!!!!!!
  • John Heizer
    John Heizer ✭
    Ancient Membership Facilitator 3 Photogenic

    Jamie Avalos:

    Does anyone know if there is an option in Queries when pulling donations from year to year that has aggregates with a + or - on each year for the totals? We have a new Chief Director and he asked for this infomation for all gifts from 2015,2016 and 2017 YTD. I didn't think Raiser's Edge had this option but just wanted to be sure before I answer?

    Time for the Blackbaud Mantra:  "Query is a grouping tool, not a reporting tool."


    You could do most of this with a Constituent Export with multiple aggregate giving columns (one for each year), then use Excel to determine if giving went up or down.

Categories