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

This method will allow you to calculate Pledges/payments appropriately. One note about the tables used here: the FT_Committed_Gifts table includes planned gifts and grants. If you do not want this counted as part of your giving statistics, you will need to filter out planned gifts and any gift without a committed date (this will filter out grants that are not connected to gifts)

1. Add a FT_Committed_Gifts Table and a FT_Pledge_Payments table
2. Either by lookup or join, bring the [Actual Amount] column from the Pledge Payments into the FT_Committed_Gifts table matching on Gift ID.
3. Add a calculated column in the FT_Committed_Gifts table and remove the nulls from the [Actual Amount] column-Zn[Actual Amount]
4. Add another calculated column and subtract: [Committed Amount]-[Actual Amount (No Null)] to get the total amount for that Gift. (Now known as [Amount for Giving Statistics])
5. Group by Contact ID, Calculate by [Amount for Giving Statistics]: This is the giving total for the Contact by whatever parameters you filter the table.

The above section will get you direct Giving and Pledges on the original Contact record. This does not account for Passthrough Giving. In order to also account for Passthrough Giving you will need to:

1. Duplicate the FT_Committed_Gifts table from above.
2. Filter on [Passthrough Contact ID]>0.
3. Group on [Passthrough Contact ID], calculate by [Amount for Giving Statistics].
4. Add the sum from the First section to a dm_contacts table matching on [Contact ID].
5. Add the sum from this section section to a dm_contacts table matching on [Passthrough Contact ID].
6. Add step 4 column to step 5 column to get + Passthrough Giving when counting Pledges as receivable. 

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.

9. 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. 

Happy Reporting!