Cognito-forms – How to calculate the Relative Rate Index based on data collected in tables in Cognito Forms

cognito-forms

I'm working on creating a Cognito form and need to know how to do a calculation with two calculated fields. I've pasted a preview below to help explain it. I want to do a calculation in the RRI column comparing the two percentages of total arrests. I'd appreciate help on how to do this.

enter image description here

Best Answer

Since I am not an expert on RRI calculations, let me state a few assumptions about what you are asking:

  1. The Arrest Rate for each ethnic group is defined as the number of arrests for that group divided by the total population of that group.

  2. The Relative Rate Index is the ratio of arrest rates for two different ethnic groups.

  3. In this case, let's also assume that the RRI is the ratio of arrest rates of ethnic groups specifically compared with White/Not Hispanic.

  4. Also, let's assume that we cannot calculate the arrest rate itself since we do not have the total population, only the RRI (tricky problem).

Assuming these assumptions are correct, let's start with the algebra necessary to perform the calculation:

White Arrest Rate = # White Arrests / (Total Population * White Population %)

Ethnic Group Arrest Rate = # Ethnic Group Arrests / (Total Population * Ethnic Group Population %)

Relative Rate Index = Ethnic Group Arrest Rate / White Arrest Rate

Now, performing a bit of substitution, we have:

Relative Rate Index = (# Ethnic Group Arrests / (Total Population * Ethnic Group Population %) ) / ( # White Arrests / (Total Population * White Population %) )

Total Population cancels out, and we are left with:

Relative Rate Index = (# Ethnic Group Arrests / Ethnic Group Population %) / (# White Arrests / White Population % )

Now, switching over to Cognito Forms calculations from algebra, we can calculate both the Percent of Total Arrests and the RRI solely using information entered into the table.

  1. First, select the option to summarize the Percent of Total City Population and Number of Arrests for Calendar Year columns, which will show the totals and make it easier to write the calculations. This also eliminates the need to capture the total number of arrests, since the table will now calculate this for us.

    enter image description here

  2. Enter the following calculation for the Percent of Total Arrests:

    =NumberOfArrestsForCalendarYear / 
Form.RacialEthnicArrestRates_NumberOfArrestsForCalendarYear_Summary

  3. Enter the following calculation for the RRI:

    =(NumberOfArrestsForCalendarYear / PercentOfTotalCityPopulation) / 
(Form.RacialEthnicArrestRates.Where(RacialEthnicGroup = "White/Not Hispanic").Sum(NumberOfArrestsForCalendarYear) / 
Form.RacialEthnicArrestRates.Where(RacialEthnicGroup = "White/Not Hispanic").Sum(PercentOfTotalCityPopulation) )

When the table is then filled out with information, the results are as follows:

enter image description here

Obviously, the RRI for White/Non-Hispanic will always be 1.00 since the numerator and denominator are the same. Please update your post the clarify the question if my assumptions are incorrect!

Finally, here is a shared template showing the form in question, with all the necessary calculations: https://www.cognitoforms.com/templates/shared/ThomasFamily/RelativeRateIndex

Related Topic