Virtuous Analytics Common Calculations

When thinking about the data in Virtuous Analytics, it’s helpful to think about it being raw data. This is extremely helpful if your organization needs to create statistics based on specific data points important to your organization. In this article, we’ve curated some of the more common scenarios you may encounter when building custom reports in Virtuous Analytics.

Table of Contents

1. Organization Groups and Tags
2. Building Controls for Organization Groups and Tags
3. Custom Fields
4. Plus Passthrough Giving using existing statistics
5. Plus Passthrough Giving limited by special factors
6. Giving Statistics When Counting Pledges as Receivable
7. Calculating Consecutive Year Giving
8. Setting Goals
9. Calculating the Fiscal Year for a Gift
10. Using Current and Previous Controls for Date Comparisons in KPIs
11. Filtering on Recurring Gifts
12. Gift Amount with International Currency using Data Models
13. Platform Reports

Organization Groups and Tags

Most fields in the Virtuous Analytics tables are 1-1 data points (for example, for Contact ID, there is only one Contact ID for each Contact). When it comes to Organization Groups and Tags, these have the potential to be a 1-Many data type. In Virtuous Analytics, that means that these data points are added as an array. In order to access the data in an array, we need to pull the piece of data out that we want to look for in the array. This would look like using the function ArrayContains(). Now, in practice that might look like ArrayContains([Organization Group List], “Major Donors”). Things to keep in mind:

• The first part of the ArrayContains() function is calling the column that has the array. The second part is the piece (e.g. Organization Group or Tag name) that needs to be pulled out. 
• The new column created will be a True/False Column that can then be used for filtering.
• You MUST type the item exactly as it is written in the field - capitalization, spacing, and spelling all matter.

Building Controls Using Organization Groups or Tags

Think of a control like a filter for a whole page. This is something you could put at the top of your workbook to make it easy for users to filter all the visualizations on the page without having to look at the tables and figure out which column will allow them to see what they need. When building controls for organization groups and tags, there are a few additional steps that need to be taken to make the control work:

• When adding tables to your workbook, in addition to adding the table you want the report built on, add the tables for either Tags or Organization Groups (depending on what your control is built on).
• The Value source for the filter will be either dm_organization_groups or dm_tags and the source column should be either Organization Group Name or Tag Name.
• The Target for the control will also be the same as the value source.
• This step assumes that you have Organization Group list or Tag list in your table. If you don’t, you will need to add via lookup or a join. In the table of the data you’re setting up the control for, add a calculated column. Add the following formulas to your calculated column, changing names for the controls if necessary:
  • If you want an OR statement to allow more than one item to be selected at a time:
    • Organization Groups: IF(IsNull([OrgGroupControl]) OR ArrayLength([OrgGroupControl]) = 0,TRUE, ArrayLength(ArrayIntersection([ORGANIZATION GROUP LIST], [OrgGroupControl]) > 0, True,False
    • Tags: IF(IsNull([TagControl]) OR ArrayLength([TagControl]) = 0,TRUE,   ArrayLength(ArrayIntersection([TAG LIST],[TagControl]) > 0, True,False
  • If you want an AND statement to allow more than one item to be selected at a time:
    • Organization Groups: If(IsNull([OrgGroupControl]) or ArrayLength([OrgGroupControl]) = 0, True, ArrayLength(ArrayIntersection([ORGANIZATION GROUP LIST], [OrgGroupControl])) = ArrayLength([OrgGroupControl]))
    • Tags: If(IsNull([TagControl]) or ArrayLength([TagControl]) = 0, True, ArrayLength(ArrayIntersection([TAG LIST], [TagControl])) = ArrayLength([TagControl]))
  • If you only want one value to be selected, you can use the following formulas:
    • Organization Groups: If(ArrayContains([ORGANIZATION GROUP LIST], [OrgGroupControl]), True, Coalesce(Trim([OrgGroupControl]), "") = "", True, False)
    • Tags: If(ArrayContains([TAG LIST], [TagControl]), True, Coalesce(Trim([TagControl]), "") = "", True, False)
• On the table, add a filter to your calculated column to only filter “True.” This will have your table only show the items you have selected in the control.

Custom Fields

Due to the variety of custom fields across databases, the best way to get custom fields into a table is to bring them in as JSON. JSON is used to make structured data easy for humans to read and for computers to use. In order to use this field, you will need to extract the data from the JSON. To do this, select the arrow next to the Custom Field Values JSON column from your table. Select “Extract” from the list. This will bring up a box with all of your custom fields where you can select the custom fields you need in your table and they will be created as new columns in the table.

Plus Passthrough Using Existing Statistics

• Add dm_ contacts and ag_contact_giving_statistics tables to your workbook
• Either by lookup or by joining the 2 tables, add any Passthrough Giving statistics to the dm_contacts table (examples: Total Lifetime Passthrough Giving, Most Recent Passthrough Gift Date, Highest Passthrough Gift Amount). Use ID (dm_contacts) and DM Contact ID (ag_contact_giving_statistics) as your key.
• To calculate the statistics, make sure you’re adding combining similar statistics (for example: Total + Total or Current Year + Current Year)
  • Giving: Add a calculated column to the dm_contacts table and add the direct giving and Passthrough Giving columns together.
    • Example: [Total Giving Lifetime]+[Total Passthrough Giving Lifetime]
  • Highest Gift Amount:
    • Remove any Null values-Add a column to the table and use Zn([Highest Passthrough Gift Amount]). Repeat for [Highest Gift Amount]
    • Then use the columns created in the last step to create a final column- Greatest([Highest Passthrough Giving(no null)],[Highest Gift Amount(no null)]
  • Last Gift date overall: Create a new column use the formula: Greatest(Coalesce([Most Recent Passthrough Gift Date], Date(1900 - 1 - 1)), Coalesce([MOST RECENT GIFT DATE], Date(1900 - 1 - 1)))

Plus Passthrough Giving Limited by Special Factors

If you need to limit which gifts get incorporated into the giving statistics, you will need to use a Ft_Gifts, Ft_Project Gifts or Ft_Gift Designations table. There are 2 methods to go about achieving this:

Method 1

Use this method if the original donor should not be included when calculating the statistics. For example, Sally’s Car Wash gives a gift with Sally Smith (the owner) as the passthrough donor. In your statistics, you do not want Sally's Car Wash included, only Sally Smith.

1. Add a new column to your gift table.
2. Name this column Donor ID or something similar.
3. For the calculation add the formula Coalesce([PT CONTACT ID],[CONTACT ID]. This will pull the Contact ID for all gifts without Passthrough givers. For gifts with passthroughs, it will pull in the Passthrough Contact ID.
4. Filter the table as needed and use the Donor ID for your calculations.

Method 2

Use this method if the original donor AND the passthrough donors should get credit for a gift. This table should not be used for overall giving statistics as it will create duplicates. For example, both Sally's Car Wash and Sally Smith will be included in the results.

1. Add 2 Gifts tables to the workbook and filter by whatever parameters you need to filter. For example, if this needs to be filtered on Project parameters, you will need to use the FT_Project_Gifts table. If the filtering can be limited by the overall Gift, the FT_Gifts tables will work.
2. In one of Gifts tables, filter only gifts with a Passthrough Contact ID greater than or equal to 1 (this ensures there is a Passthrough ID)
3. Union the 2 tables together. When creating the Union, match the Contact ID to PT Contact ID and DM Contact ID to PT DM Contact ID.
4. In your new table, you can now group by Contact ID to build giving statistics including:
   • Overall Giving: SUM[Gift amount]
   • Last Gift Date: Max[Gift Date]
   • Largest Gift: Max[Gift Amount]

Giving Statistics When Counting Pledges as Receivable

If your Pledge Payments are less than or equal to the Expected Pledge Payment Amount, we recommend using the FT_Commited_Gifts Table in the place of the process below. Note that the FT_Committed_Gifts Table also includes Grants and Planned Gifts. If Grants and/or Planned Gifts should not be included in the output of Giving Statistics, you will need to filter those out as well.

Use this method when Pledge Payments never exceed the Pledge Amount and no special circumstances need to be considered (like giving to particular Projects):
 

1. Add a FT_Committed_Gifts Table to a Workbook.
2. Filter the FT_Committed_Gifts Table to exclude Pledge Payments.
3. Then, group by Contact ID, Calculate by [Gift Amount]: This is the giving total for the Contact by the parameters you use to filter the Table.

For counting Pledges as receivable where there may be Gifts with Pledge Payments greater than the Expected Pledge Payment Amount(s), or special circumstances should be considered we recommend using the following steps:

1. Add a FT_Gifts Table and a FT_Pledge_Payments Table to a Workbook.
2. Either by Lookup or Join, bring the [Actual Amount]column from the Pledge Payments into the FT_Gifts Table matching on ID (FT_Gifts) and DM Gift ID (Pledge Payments).
3. Add a calculated column and subtract: [Gift Amount]-Zn([Actual Amount]) to get the total amount for that Gift. (Now known as [Amount for Giving Statistics])
4. Add an FT_Pledges Table to the workbook.
5. Union the FT_Pledges Table to the FT_Gifts Table. All Pledge fields should be matched to the Gift fields (i.e. Pledge Date = Gift Date; Pledge ID = Gift ID; Contact ID = Contact ID, Pledge Amount = Gift Amount, etc). This treats the Pledges as Gifts.
6. Then, group by Contact ID, Calculate by [Amount for Giving Statistics]: This is the giving total for the Contact by the parameters you use to filter the Table.

Keep in mind that the above section will only uncover direct Giving and Pledges on the original Contact record.

To include Passthrough Giving, use this method listed above which will only include the original donor. Or, alternatively, use this method if the original donor AND the passthrough donors should get credit for a gift. This table should not be used for overall giving statistics because there will be duplicates.

1. Duplicate the table created above
2. In one of the tables, filter only gifts with a Passthrough Contact ID greater than or equal to 1 (this ensures there is a Passthrough ID).
3. Union the 2 tables together. When creating the union match the contact information to the passthrough information.

Calculating Consecutive Year Giving

To calculate consecutive years giving you will need two Gifts tables. This could be done with ft_gifts, ft_project_gifts, or ft_gift_designations. For the purposes of this example, we’ll use the ft_gifts Table. Also, keep in mind that if you want to include Passthrough Giving, your Tables will need to reflect that.

1. Add ft_gifts Table to your Workbook.
2. Group by Dm_ContactID.
3. Create a second grouping by Gift Date.
4. Truncate Gift Date to year of Gift Date.
5. In the calculation of the second grouping:
   1. Calculate the year of the max of the gift date: DateTrunc("year",Max([GIFT DATE])) . This will remove any duplicate Gift Dates if a donor gave more than one time in a given year.
   2. Sort the Year of Max of Gift Date column (step a) in ascending order. This step is incredibly important- if you skip this step, this entire process will not work.
   3. Add a calculated column to determine the lag of Year of Max of Gift Date: Lag([Year of Max of Gift Date],1)This shows the year that the donor previously gave- dependent on step b.
   4. Add a calculated column that flags when there is a gap in giving, we’ll rename this column “Break Flag”: If(Year[Year of Max of Gift Date]- Year([Lag of Year of Max of GIFT DATE])=1,0,1)
   5. Using the Break flag column, add a calculated column that assigns an ID to each group of years without a break, name this column Streak Group ID: CumulativeSum([Break Flag])
6. Add a new ft_gifts Table to your Workbook.
7. Group this table by Dm_ContactID.
8. Add a column via lookup using Streak Group ID from the previous table. Match on DM_Contact ID AND Gift Date.
9. Group by Streak Group ID.
10. In the calculation section of the Streak Group ID Grouping:
    1. Calculate the count distinct of the year of the gift date: CountDistinct(Year([GIFT DATE])). The Streak Group ID identifies all the gifts with the same ID but doesn’t filter out duplicates. By using CountDistinct you are only looking for unique years of giving in that group.
    2. Optional: Calculate the Max of Year of Gift Date: Max(Year([GIFT DATE])). This would allow you to filter the table to only donors who have given in the current year and their current giving streak.
    3. Optional: Calculate Active donors by using an If statement to formulate who are active Donors: IF(Max([GIFT DATE])>=DateAdd("year",-1,Today()),"Active","Inactive"). This statement would allow you to see anyone whose last gift in your table was in the last rolling year.

Setting Goals

If your organization has set goals that are not included in any Tables for Contact Types, Campaigns, Projects, Organization Groups, or generally any piece of data, you can create goals and track percentages towards those goals. Here is an example of setting goals based on the Contact Type:

1. Add a DM_Contact Table.
2. Group by Contact Type.
3. Add Calculations to calculate SUM[TOTAL GIVING LIFETIME]. This is the number to track towards the goal we will set.
4. Add 2 more custom columns to Calculations: Overall Goals and Percentage to Goal. 
   1. To set the Overall Goals values, enter the logic: fx| If ({CONTACT TYPE] = “Foundation”, 50000, [CONTACT TYPE] = “Household”, 100000000. For each item that has a set goal, create the condition in the same format.
   2. To calculate the Percentage to Goal values, create the condition: fx| SUM[TOTAL GIVING LIFETIME]/[Overall Goals]
   3. Change the format for the column to Percent type. 

Calculating the Fiscal Year for a Gift

1. Pull in the ft_Gifts table (or Designations if you want to ultimately filter/group by project).
2. Add a column; name it something like “Fiscal Year.”
3. For the function (if FY is July-June): If(Month([GIFT DATE]) >= 7, Year([GIFT DATE]) + 1, Year([GIFT DATE]))

Using Current and Previous Controls for Date Comparisons in KPIs

You can create comparisons for various date ranges such as quarters, fiscal years, or rolling years with KPIs. For the purposes of this example, we will compare this year's giving total with last year's giving total.

1. Add your dataset to your workbook with any additions or custom calculated columns needed. Then duplicate this table, so you have two identical datasets. For our example, we’ll add the ft_gifts table and then create a copy of it.
2. Edit the titles of each table to include “Current” and “Previous” into the corresponding titles to make it easier to differentiate the two tables.
3. Add a Date Range control to your workbook. Modify the date range to be the current year. Edit the label to include “Current” as well. Then, in the element properties, target the Gift Date column in the “ft_gifts Current” table.
4. Next, add a second Date Range control to your workbook. Modify this second date range control to the previous timeframe (last year). Edit the label to include “Previous”. Then, in the Element Properties, target the “ft_gifts Previous Year” table using the gift date column.
5. Then, insert a KPI from the Charts icon. Select “ft_gifts Current" as the Data Source. In the KPI properties, add the Gift Amount column to the Value section and ensure the Aggregate Values checkbox is selected. This will total your gift amounts from the current year.
6. Lastly, in the Comparison section of the KPI properties, select the + icon and “Add a new column”. Enter the formula SUM([ft_gifts previous/GIFT AMOUNT]) . This formula pulls the corresponding column from the previous timeframe’s table.

Filtering on Recurring Gifts

If you want to be able to view only Recurring Gifts or exclude Recurring Gifts in your Gifts table, you will need to bring that information into the Gifts table from the Recurring Gifts table.

1. Add the ft_gifts and ft_recurring_payments tables to your workbook.
2. In the ft_recurring_payments table, add a calculated column. Use the formula If([Actual Amount] >0, “True”,”False”). This will indicate that a payment has been made on that expected payment. Name this column something like [Is Recurring?]
3. In the ft_gifts table, add the [Is Recurring?] column via lookup matching on ID and DM Gift ID
4. Now, in the ft_gifts table, you can filter on whether or not the Gift is a Recurring Gift.
5. Alternatively, if it is possible that someone gave a Recurring Gift and an Outright Gift in the same Gift, you could bring in the actual amount via lookup from the Recurring payments table to the Gifts table. 
   • Add a new column and use Zn[Actual Amount) to replace nulls with 0’s.
   • Add another column and in this column subtract the recurring payment amount from the Gift amount. [Amount]-[Actual Amount-No null] This will give you the amount paid above the Recurring Payment.

Gift Amount with International Currency using Data Models

Analytics uses two types of tables, Datasets and Data Models. When using Datasets, Gift Amount columns show the converted amount of all Gifts into your organization’s currency. When using Data Model, the Gift Amount columns do not convert the Gift Amount automatically, and instead show the Gift’s original currency. This means a Gift in a foreign currency will show the amount in the base currency. 

Dataset vs Data Models

When adding a Dataset table into your report, the dataset always has a stacked layers icon and begins with a prefix (e.g. dm_). Datasets type tables also live within the folder titled Datasets.

The Data Model tables function similarly as centralized data sources for your reports. However, they provide only raw data related to each data source. This allows for even further customization in your reports through joins and lookups. When adding a Data Model table into your report, the table always has a stairs icon and comes from Virtuous CRM+ Source Tables folder.

Converted Gift Amount Calculation

In order to get accurate giving totals if your organization accepts any donations from outside of US dollars, you will need to create a calculation on Data Model tables to find the converted amount. To do so, create a calculated column using [Amount]*[Exchange Rate]. 

Platform Reports

If you see something in one of the app reports that you’d like to use in your custom Virtuous Analytics report, remember that you can always select Save As at the bottom right corner of the report, then you can edit the copy of that report to see how the controls, or formulas were created to use as a basis for your own calculations. 

Need More Help?

Need more help making sense of your data in Virtuous? Virtuous Professional Services can create custom Virtuous Analytics Reports for your organization. Whether you have a Report or visualization in mind, or need something completely new, our team can scope your needs and build Reports to give you the insights you need. Click here to learn more about our Professional Services offerings, or contact your Customer Success Manager to get started!

Happy Reporting!