Tableau Training

Ranking Cohorts by Who Donated the Most Each Year

A cohort is a group of donors who gave for the first time the same year. This workbook ranks cohorts of donors by which group donated the most year to year.

Concourse Hosting covers Ranking Cohorts by Who Donated the Most Each Year in this free Tableau training workbook.
  1. Data Source Data Source 1
  2. Marks Marks 2
  3. View View 3
  1. Data Source

    Dimensions:

    Cohort, Gift Date, Gift Type, Gifts ID, Gift Records ID

    Measures:

    Cohort Rank, Gift Amount, Show Cohort Rank, Year Rank

    Calculated Fields:

    Cohort

    'Year of ' + STR(datepart('year',{FIXED [Gift Records ID] : MIN([Gift Date FY]) }))

    I used Tableau's FIXED level of detail to calculate the cohort. What this does is look at the minimum gift date across all donor records. I then get the year datepart of that date. This effectively groups them together into bins based upon the year they first gave.

    Cohort Rank

    Index()

    Ranking is done using the Index function. There's a table calculation applied along each Cohort and sorted by the sum of donations descending (higher amounts first) for each gift year.

    Year Rank

    Index ()

    All this one does is rank the years by latest one first. It's used in the calculation below!

    Show Cohort Rank

    IF [Year Rank] >= [Cohort Rank] THEN [Cohort Rank] END

    This field is trick I use to hide empty cells, like for the 2009 cohort in the year 2008. Since any cohort would not have given prior to their cohort year, I want to hide all cells that meet that criteria.

    Donations

    {FIXED [Gifts ID] : MIN([Gift Amount])}

    This is a simple level of detail calculation to get a single gift amount for each gift id. It's only necessary to do if your data are denormalized where you have multiple rows for each gift id.

    Percent of Donations

    IFNULL(SUM([Donations]) / TOTAL(SUM([Donations])),0)

    This calculation lets us know what percentage of the donations came from one cohort.

  2. Marks

    Color:

    SUM(Donations)

    Tooltip:

    Percent of Donations

    Label:

    Show Cohort

  3. View

    Columns:

    Year(Gift Date FY)

    Rows:

    Cohort

They key to this visualization is the table calculations! Be sure to take a look at how I've applied table calculations to each pill.

Next steps

When preparing your own data set for use with this workbook, be sure to include both an ID associated with each gift (Gifts ID) as well as an ID for each person (Gift Records ID).

Book Tableau training
for your nonprofit or foundation

Message sent!

We will respond shortly