In this guide, we cover some more advanced examples of using the expression language in mWater for your data analytics.
Site -> Many survey responses and not just the latest
How many surveys has each team filled out per month?
A single site can have any number of survey responses linked to it. Expressions beginning from the site as a data source default to pointing at the latest response (the latest survey response is the one that has the most recent Submitted On date). However, in some cases, you may want to run expressions that refer more specifically to some or all of the survey responses linked to a site.
In this example, we'll look at how you can start from a site and count all the surveys that have been filled out in the last 30 days per data collection team. We'll build a pivot table to show this.
Step 1. Select the site as the data source for your pivot table
Step 2. Select the data field your want to aggregate by as the row (You can use any relevant field: team, site name, type, etc.)
Step 3. Build the expression for the data column of the pivot:
3.1 Select the formula for Total.
3.2 Select the Advanced... option at the bottom of the related Survey
3.3 For the next step, select the formula Number where
3.4 Pick the date field from the survey and set it to the last 30 days
Total + Related Survey -> Advanced... + Number where + date field is in last 30 days
Explanation
We can unpack this starting from the end:
The Number Where expression counts the amount of surveys conducted per site where a condition applies. In our case, the condition is that the survey has been filled in within the last 30 days. We now have a number for each site the team has visited.
For example:
Team A - Site 1: 10
Team A - Site 2: 12
Team A - Site 3: 26
We have used the Advanced... option at the bottom of the choices for the related survey to tell the system that we want to run an expression at the survey's own level, and then bring that value to the operation we have created before the Advanced step.
The Total expression takes the number of surveys matching our given condition from the Number where step, and sums the total across each site.
The rows we have specifiedin the pivot table tell the system to group these totals by Team.
For example:
Team A: 10 + 12 + 26 = 48
Without the Advanced step, the expression would amount to: Show me all the survey responses where the Latest survey response of the whole set for the row is within the 30 day limit. With the Advanced step, we run a query on the whole set of survey responses relevant for that row (site, team, etc) and include only the ones that match the where-condition, so in this case all those where the date question value falls within the last 30 days.
The Advanced... option can be used in similar situations to cater for many use cases.
Recap: Total + Related Survey -> Advanced... + Number where + date field is in last 30 days
Split Axis chart to changes over show time
Bar charts can be a good way to show regular data collection over months, quarters or years. By default they just show one dimension of data mapped per unit on the horizontal axis. With the Split axis feature.
There you pick the data field you want to split the bar chart bars by. This can be useful in showing the options of a single-select field from a survey all at once, and how the responses change with each visit, for example.
In this example we'll work through how to show water point functionality statuses by month for a country.
Step 1. Create a bar chart widget with the water point functionality status
Step 2. Set the date as the horizontal axis and set to show it by month. The vertical axis will be the count of water points by default.
Step 3. Set the Split axis option further down the settings page to point to the Functionality Status field.
Then adjust the colours and other options like showing the bars stacked or proportional to tweak your visualization.
