Virtuous BI Common Calculations

When thinking about the data in Virtuous BI, 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 BI.

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. Setting Goals
8. Calculating the Fiscal Year for a Gift
9. Filtering on Recurring Gifts
10. Platform Reports

Organization Groups and Tags

Most fields in the BI 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 BI, 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

This method is to be used when the existing giving statistics won’t work (for example, when someone wants to see a different time period or if they want to see this in regards to only particular projects).

1. Add 2 Gifts tables to the workbook and filter by whatever parameters you need to filter. 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 the Gifts tables, group by Contact ID. (Special Note: If you want to see Sally's Car Wash AND Sally Smith get credit for a gift in BI, do not filter this table. However, if ONLY Sally Smith should get credit for the gift, then on this table, filter out any gifts with a Passthrough Contact ID. If you do not filter, keep in mind your overall totals will not be correct)
3. In the other Gift table, filter only gifts with a Passthrough Contact ID greater than or equal to 1 (this ensures there is a PT ID)
4. Union the 2 tables together. When when creating the Union, on the Table used in step 3, where the table matches Contact ID to Contact ID, change that Field (and all related ones) to Passthrough Contact ID.  You will also need to remove the PT Contact ID row by selecting the x beside the title of the row. Then, scroll down and add it back to the table (we need to remove the row because it is the only way to remove the duplication of the PT Contact ID, however, we need to add it back because we need the data from the original table because we still need to only show gifts with no PT Contact ID).                             
5. In your new table, group by Contact ID and then do the following calculations:
   • 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 FT_Gifts 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.

1. Add a FT_Committed_Gifts Table and a FT_Pledge Table to a Workbook.
2. Filter the FT_Commited_Gifts Table to exclude Pledge Payments.
3. Union the FT_Pledges Table to the FT_Commited_Gifts Table. All Pledge fields should be matched to the Committed Gift fields (i.e., Pledge Date = Gift Date; Pledge ID = Gift ID; Contact ID = Contact ID, Pledge Amount = Gift Amount, etc.).
4. 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), 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:

1. Duplicate the final output from either set of steps above.
2. Keep one of the Tables grouped by Contact ID.
   1. (Note: If you want to see Sally's Car Wash AND Sally Smith get credit for a Gift, do not filter this Table. However, if ONLY Sally Smith should get credit for the gift, then on this Table, filter out any gifts with a Passthrough Contact ID. If you do not filter, keep in mind your overall totals will not be correct.)
3. In the other Gift Table, remove the grouping, filter only Gifts with a Passthrough Contact ID greater than or equal to 1 (this ensures there is a Passthrough ID).
4. Union the 2 Gift Tables together. When creating the Union, on the Table used in Step 3 that matches Contact ID to Contact ID, change that Field to Passthrough Contact ID (every field on the Table with Passthroughs only filter needs to match PT Contact fields to Contact fields on the opposite Table). You will also need to remove the PT Contact ID row by selecting the X beside the title of the row. Then, scroll down and add it back to the Table.
   1. This removes the duplication of the PT Contact ID; however, because you still need data from the original Table to only show Gifts with no PT Contact ID, it is important to add it back.

       5. In your new Table, group by Contact ID and include the following calculations:

• • Overall Giving: SUM[Gift Amount]
  • Last Gift Date: Max[Gift Date]
  • Largest Gift: Max[Gift Amount]

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])

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.

Platform Reports

If you see something in one of the platform reports that you’d like to use in your custom Virtuous BI 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 BI 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!