This is my data where I’m calculating step count for a person, I have multiple residence_id…this is for one such resident.
I want to calculate total steps in a day and then compare if the steps are more than a certain value (suppose 1000 in this case); if steps are more then the person has met their daily step goal and if not then failed. Finally I want to find the percentage of people who completed their step goals for the day.
I’m able to calculate the step count of a person for a day : max({metric_value}) ——> this is working
and then compare if its greater than 1000 then true else false: ifelse(max({metric_value})>avg(1000),1,0) ——> this is working
but then afterwards getting errors in when try to count the resident with value true: count(ifelse({trial: step goal tf}=1,{residence_id},NULL)) ——> error in this [Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed.]
so that I can divide it by total count of residents and get the percentage.
I often get the nested aggregation errors
Please help me!
My first suggestion for you is: Avoid nesting aggregate functions in calculated fields, as qs does not support this. Always create intermediate calculated fields.
That said, to calculate the percentage of residents who achieve their daily step goal in qs, first, for each resident and each day, calculate the total number of steps taken: this can be done by creating a calculated field using max({metric_value}).
Next, you’ll need to determine whether each resident has met the daily goal. You can do this by creating another calculated field that checks if the total steps exceed 1000: ifelse(max({metric_value}) > 1000, 1, 0). This will give you a value of 1 for residents who have reached the goal, and 0 for those who haven’t.
After that, count the total number of unique residents for the day using distinct_count({residence_id}). To find out how many residents actually achieved the goal, simply sum the values in your step goal field: sum({step_goal_achieved}).
Finally, to work out the percentage of residents who met their step target, divide the number who achieved the goal by the total number of residents, and multiply by 100: sum({step_goal_achieved}) / distinct_count({residence_id}) * 100.
it didn’t work, when doing sum({step_goal_achieved}) it throws error of nested aggregation.
step_goal_achieved calculation is ifelse(max({metric_value}) > 1000, 1, 0)
Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.
