1

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

  • 1
    An image is great as supplemental information, but not everybody can see images. Your question would be much better if it had all of the needed information without the image, so that the image could act as supplemental information. – ale Dec 7 '16 at 20:49
-1

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

| improve this answer | |
  • Thank you! I will try that and I think your comment will help me simplify some of my other tables. – Heidi Cooper Dec 7 '16 at 21:29
  • Your suggestions helped a lot! However, I'm running into a problem. I created columns to calculate racial and ethic populations from the percentages and added a calculation column for arrest rate (Arrest Number/Ethnic Population * 100,000). I should be able to get the RRI by comparing the arrest rates but I'd like the formula to work for any lable they enter. I tried this formula = ArrestRate2/ArrestRate2.Where(RaceEthnicity = "White/Not Hispanic") but get an error message - No applicable method 'Where' exists in type 'Decimal' at character 25. Can you tell me what is wrong? – Heidi Cooper Dec 8 '16 at 21:28
  • Please submit a support request to Cognito Forms, mention my name, and I will take a look at your form and provide specific advice! cognitoforms.com/supportrequest – Jamie Thomas Dec 9 '16 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.