Am creating a form to monitor number of beneficiaries registered by individual community workers. The aim is for individual community workers to submit the number of beneficiaries successfully registered per month. I want to know:
1. How to add the number submitted in the previous month to the number being submitted this month - something like coalesce function but adding the cumulative sum longitudinally across subsequent forms submitted over time. Screenshot below:
2. What parameters I need to specify in order for the heat map under Data to show a cumulative total of responses submitted so far for a particular sub-district (aka LLG), instead of individual data points submitted per form. Screenshot below:
Would also appreciate advice on whether 1. is currently possible in Kobo. And whether the better option for 2. is to use API to connect to Power BI for the kind of heat map I am trying to show.
Thank you in advance for any tips and guidance folks here might have.
@coboni Welcome to the forum
Please note that the coalesce() function only works within a single form submission (e.g., selecting the first non-empty value between two questions in the same form). It cannot aggregate or pull data across multiple longitudinal submissions over time.
If you need to pull data from previous submissions into a new form, you can use Dynamic Data Attachments. Alternatively, if you need to merge longitudinal data for an individual after collection, we recommend using a unique ID question and merging the dataset post-export using other tools like Excel, R or Python.
A cumulative total across monthly submissions sounds like it may need to be handled outside the basic form calculation, especially when each submission is a separate record. For the heat map, grouping the data by LLG and calculating the cumulative total in Power BI could be a cleaner approach. The API route also gives you more flexibility if you want the dashboard to update automatically over time.
@jorcabrera thank you for clarifying, this confirms my suspicion that it might be easier to work with the data outside of Kobo. Since we have a cascading list that allows each community worker to select their name after selecting their LLG, we might elect to work with the data in Power BI and filter by community worker name to show total number of beneficiaries each has registered. Appreciate this clarification and your suggestions!
@ashad_7 thank you for articulating this, I had thought this would be the way to go but was unsure and came here to seek the views of the community. Appreciate this confirmation!
