I am hoping that someone can help me with a way to accurately display information I am capturing in Creator into Reports. The just of my form is that the user fills in the number of safe and unsafe observations of a given activity. The sample data would be as follows:
1. 3 safe, 2 unsafe
2. 90 safe, 10 unsafe
3. 1 safe, 1 unsafe
I have built in formulas behind the scenes of the form to calculate the total number of observations and then also the % unsafe of each record. So for these records it would be 40%, 10%, and 50%. In reports I want to be able to track over time the % of unsafe behaviors occurring. Thus my x-axis is full date. For the y-axis, I pull the %, however I have no good choice in terms of what to do with these values. I obviously don't want a max, min, sum, etc. I need to do a weighted average. Assuming all three of these records were for the same date, the correct weighted percentage the report needs to display is 12% (13/107). However Reports will just average the 40/10/50 and return 33%.
Can anyone suggest how to accomplish this? I'm not sure whether or not the changes need to be on the Creator or Reports side.