Forum Discussion

EraldoEstrada's avatar
EraldoEstrada
Qrew Member
2 months ago

Calculating Percentage Average of Consent Date Entries: Seeking Formula Assistance

In our data analysis task, we encounter a scenario where we need to determine the average percentage of Consent Date entries compared to Date Requested entries in a table.

The steps are: 

  1. Count the total number of entries recorded in the Consent Date field.
  2. Count the total number of entries recorded in the Date Requested field. 
  3. Calculate the ratio of Consent Date entries to Date Requested entries. 
  4. Convert the ratio into a percentage average. 

All my values are entered into the table, but I don't have the know-how to write the formula.

I imagine it would be a table report generating this percentage. Thank you for your wisdom.



------------------------------
Ev von Cort
------------------------------

1 Reply

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 months ago

    You can do it a couple ways, long and short is you first need to summarize the counts as you've highlighted in (1) and (2). 

    Option 1 is a 'Master' table - in which you create a single record table known as a master record, and define a relationship where each record is related to this master record, then using summary field count the # of records with each date populated then just do counter 1 / counter 2

    Option 2 is to use a summary report and do something like: 

    • Create 1 field that is formula numeric that is the value of 1 when request date is populated, or 0 if it's not.
    • Create another field with the same logic - just based on consent date

    You can then take those counter fields and use them in a summary report where you summarize those counter values, then using a summary report formula do counter 1 sum / counter 2 sum



    ------------------------------
    Chayce Duncan
    ------------------------------