4 Replies Latest reply on Jun 6, 2014 8:31 PM by Shawn Wallwork

    Partitioning and Addressing with Table Calculations

    Ben Leathers

      In the attached workbook I am leveraging a database that list donations received for the last 3 years.

       

      Each row has a donor ID, donation amount, Year of event, and whether they have donated to previous events or not (and other columns irrelevant to this question).

       

      I am trying to look at donors above the 99th percentile for each year to see how many have donated to previous events, and how many are donating to their first event.

       

      I have a table calculation that counts the donor ID above the 99th percentile, but I can't seem to figure out how to use the partition and addressing fields to display them correctly. I can only seem to see the top percentile of First Year Donors in 201X, and the Top Returning Donors of 201X, and what I would like to see is the top percentile of 201X, and how it is distributed between First Year Donors and Returning Donors.

       

      e.g.

       

      If I had a spreadsheet that looked like the following, and I wanted to count the donor ID's above the 60th percentile for each year, and see their distribution between First Year and Returning Donors

      Donor ID

      Donation Amount

      Event Year

      Returning Donor

      1

      52013

      First Year Donor

      252013First Year Donor
      352013Returning Donor
      4102013First Year Donor
      5102013

      Returning Donor

      6

      5

      2014Returning Donor
      752014

      First Year Donor

      852014Returning Donor
      9102014Returning Donor
      10102014

      Returning Donor

      _201320132014

      2014

      First YearReturning YearFirst Year

      Returning Year

      Count # of Donor ID's3214
      Count # of Donor ID's above 60th Percentile of each Year1102

       

      I hope that makes sense.

       

      Thanks for any help!

       

      Message was edited by: Ben Leathers Oops... thanks for letting me know @shawnwallwork !