Data Source: Populate from a data table

After you have created a data table, you can design indicators that pull results directly from the data table. This article covers how to: 

Define a data table indicator

Begin on the indicator's definition page. For data source, select populate from a data table and choose the table that contains information for this indicator. 

Next, select the calculation. You can either count all rows, count unique sets of columns, or get a sum or average of a column of numeric data. Additional configuration options will depend on which calculation type you use. This example shows the count all configuration.

If the table has only one geography column and one date column, these will be selected by default. Otherwise, you must choose which geography and date columns should be associated with the indicator results. (For example, you would probably choose to report your indicator results by "date of training" rather than "date of birth" for the beneficiary.)

Finally, you can choose to exclude rows from your calculation by applying one or more filters. See the filter section at the bottom for more information. A filter should express the data that you want to include. You can create a filter based on any field from a data table, though it is not advisable to use "Other Text" fields as spelling errors, typos, or bounding quotation marks (" ") will yield unexpected results. Filters can use one of four operators: = (equal-to), ≠ (not equal-to), has a value (not null), or has no value (null). In this example, the indicator will only count trainees who have a final test score reported. You could also count only female trainees with a filter that says "Sex = Female", or select only those trained in business administration with a filter that says "Training Type = business administration". 

The rest of the indicator's definition works like any other indicator with three caveats:

  • Any disaggregations assigned to the indicator must be included in the data table.
  • The reporting level must either be the same as the geography column selected in the data source section or a reporting level that's less specific. For example, if the data source geography column is "district", the indicator could be reported per country, but not per location. 
  • If the indicator is reported per activity, there must be an activity column in the data table.

Note: any rows missing data in a column relevant to the indicator definition (such as the date, geography, or a disaggregation) will be ignored when indicator results are calculated. 

back to top

Data table indicator calculation: Count all

The Count All calculation counts all rows in your data table. (The same caveats apply for any indicator: rows of data are excluded from the calculation if they don't have complete information for all columns relevant to the indicator definition, or if filters are applied).

To create a count-all indicator, set the data table Calculation to Count All.

The result for this indicator mapping will be a count of all the rows in the data table.

Have a look at this User Trainings data table:

There are 20 rows, so Count All would give the result of 20. 

back to top

Data table indicator calculation: Count unique sets

The Count Unique Sets calculation counts unique values, or sets of values, in your data table. (The same caveats apply for any indicator: rows of data are excluded from the calculation if they don't have complete information for all columns relevant to the indicator definition, or if filters are applied). 

How Does 'Count Unique Sets' Work?

Where Count All counts all rows in a data table that meet your indicator filters, Count Unique Sets looks at the combination of the Count Unique Columns and will count only the unique rows defined by those criteria. So, for example, if you have a Training table with a Count Unique set up to look for First Name + Last Name, if you train the same person multiple times, they will only be counted once.

Have a look at the training log below. In this example:

  • Count All would give the result of 10. There are 10 rows.
  • Count Unique Sets for the Trainee column would give you the result of 4. There are 4 different values in this column:
    • leia@devresults.com
    • chewbacca@devresults.com
    • han@devresults.com
    • luke@devresults.com
  • Count Unique Sets for both the Trainee and Training Type column would give you the result of 6. There are 6 unique pairs of values in the Trainee and Training Type columns:
    • leia@devresults.com / Swordswmanship
    • chewbacca@devresults.com / Launching & Landing
    • han@devresults.com / Launching & Landing
    • han@devresults.com / Swordsmanship
    • luke@devresults.com / Swordsmanship
    • chewbacca@devresults.com / Swordsmanship

Configure a Count Unique Sets Indicator

To create a count-unique-sets indicator, set the data table Calculation to Count Unique Sets.

Once you select Count Unique Sets in the calculation, this will add some Count Unique Column(s).

By default, if the indicator is set for Results are reported separately for each activity, the Activity column will be added and not removable from the Count Unique Columns. (To remove Activity here, you will need to uncheck the Results are reported separately for each activity box in the Disaggregations section.)

If any disaggregations are assigned to the indicator, these will also be automatically added to the Count Unique Columns. To remove the disaggregation from the Count Unique Columns, remove it from the Disaggregations section of the indicator.

Add any columns that uniquely identify your rows. For example, this definition will take the Activity + Trainee First Name + Trainee Last Name to determine uniqueness.

Note: If your indicator is set to report at any Reporting Level below your top level, you will need to include a Geography column from your data table in your Count Unique Columns for it to calculate properly. If you don't, you'll see a warning like this:

To fix this error, simply change your reporting level or add the appropriate geography column to your Count Unique Columns list.

For the Dates, you have two options:

  • If you do not include a Date column in your Count Unique Columns list, all data for this indicator will be associated with the most recent date in the Date column.

  • If you want rows to be calculated based on their date and put into differing reporting periods, etc., you must add the Date column to your Count Unique Columns list.

To see this in action, let's return to our earlier Training Log example:

If we do not include the Date column in our Count Unique Columns list, all calculations for this indicator will be associated with 04 May 2014, the most recent date in the Date column. Count Unique for both the Trainee and Training Type column would give you the result of 6 as of 04 May 2014:

  • leia@devresults.com / Swordswmanship
  • chewbacca@devresults.com / Launching & Landing
  • han@devresults.com / Launching & Landing
  • han@devresults.com / Swordsmanship
  • luke@devresults.com / Swordsmanship
  • chewbacca@devresults.com / Swordsmanship

If we do include the Date column in our Count Unique Columns list, Count Unique for the Trainee, Training Type, and Date column would give you the result of 9:

  • leia@devresults.com / Swordswmanship / 04 May 2014
  • chewbacca@devresults.com / Launching & Landing / 04 May 2014
  • han@devresults.com / Launching & Landing / 04 May 2014
  • han@devresults.com / Swordsmanship / 03 Jan 2014
  • chewbacca@devresults.com / Launching & Landing / 03 Jan 2014
  • han@devresults.com / Launching & Landing / 03 Jan 2014
  • luke@devresults.com / Swordsmanship / 03 Jan 2014
  • leia@devresults.com / Swordsmanship / 03 Jan 2014
  • chewbacca@devresults.com / Swordsmanship / 03 Jan 2014

Troubleshooting Tips for Count Unique Columns

  • If you don't count per unique date, the calculation results are assigned to the most recent date in the data table.
  • If you don't count per unique geography, the calculation results are assigned to the largest geographic division in the system, such as the whole country or the whole world.
  • If the indicator is designated to be reported per activity, then the calculation generates separate unique counts for each activity.
  • You can still add filters as usual so that the indicator calculation ignores any rows of data that do not meet the filter criteria.

back to top

Data table indicator calculation: Sum

The Sum calculation gives a total of a numeric column in your data table. (The same caveats apply for any indicator: rows of data are excluded from the calculation if they don't have complete information for all columns relevant to the indicator definition, or if filters are applied).

To create a sum indicator, set the data table Calculation to Sum and select the column of data to be totaled. The dropdown only includes columns defined with numeric formats.

What result do I get from Sum?

The result for this indicator mapping will be a sum of the values in the Number Days Attended column.

Have a look at the User Trainings data table below. Sum will give the total result of 64. Split out by reporting period and activity:

  • 2016 Q1 (all the rows with locks):
    • Definitive Data Doing: (4+4+4+2+4+4+4+2+2) = 30
    • Training Extravaganza: (3+2) = 5
  • 2017 Q1: Friendly Data Help: (4+3+4+3+4+3+4+2+2 ) = 29

Since this data is reported per Location, per Activity, and is disaggregated by Gender, you can see it displayed that way in any of the visualizations.

back to top

Data table indicator calculation: Average

The Average calculation gives an average of a numeric column in your data table. (The same caveats apply for any indicator: rows of data are excluded from the calculation if they don't have complete information for all columns relevant to the indicator definition, or if filters are applied).

To create an average indicator, set the data table Calculation to Average and select the column of data to be averaged. The dropdown only includes columns defined with numeric formats.

What result do I get from Average?

The result for this indicator mapping will be an Average of the values in the Number Days Attended column.

Have a look at the User Trainings data table here:

Average is calculated by adding together the values and dividing by the number of values. So for the data above, you'll get:

  • 2016 Q1 (all the rows with locks):
    • Definitive Data Doing: (4+4+4+2+4+4+2+4+4+2+2)/9 = 3.33
    • Training Extravaganza: (3+2)/2 = 2.5
  • 2017 Q1:
    • Friendly Data Help: (4+3+4+3+4+3+4+2+2)/9 = 3.22

Since this data is reported per Location, per Activity, and is disaggregated by Gender, you can see it displayed that way in any of the visualizations.

back to top

Apply filters

Indicators populated from a data table provide the option to filter a data table to include only the rows of data relevant to your indicator calculation. You might not need any filters, or you might need one or more filters depending on your data table and your indicator. 

Filters are a way of ignoring any rows of your data table that do not meet the criteria that you define. You can create a filter based on any field from a data table, though it is not advisable to use "Other Text" fields as spelling errors, typos, or bounding quotation marks (" ") will yield unexpected results. Filters can use one of four operators: = (equal-to), ≠ (not equal-to), has a value (is not null), or has no value (is null).

In this case, the indicator will include in its calculation only the rows where the column Number Days Attended is greater than 0.

You can also filter information based on whether or not a selected column has a value (is not null) or has no value (is null). 

If you want to only include rows for which the selected column has a value, you would add a filter for the indicator to only count a value in the selected column if it has a value. If you want to only include rows that are empty in the selected column, you would add a filter for the indicator to only count a value in the selected column if it has no value

In the example below, instead of counting all trainees, the indicator is counting only a specific set of trainees: 

  • Those who are female,
  • and where the training type was filled out,
  • and the training topic was "Technology",
  • and the trainee improved more than 10 percentage points. 

Any rows of data (i.e. any trainee that doesn't meet ALL of that criteria) would be excluded from the count. (To count all trainees, remove all the filters.)

Troubleshooting

A common error arises when using multiple indicator filters on the same data table column. For example, if you want to create an indicator that counted rows where the service type is "nutrition" or "health", you might include two filters: 

  • Service Type = Nutrition
  • Service Type = Health

The problem is that no rows could have both nutrition and health for the service type at the same time. Any individual row could only have either nutrition or health as the service type, but not both. Since all of the filters must be true for a row in order to count that row toward the indicator result, this configuration would give you zero results. 

How do you fix it? Look a the disaggregation categories for this disaggregation. 

The indicator needs to count rows where the service type is equal to two of these categories, which is the same as not equal to the other two categories. Instead of filtering using the categories you want to include, filter out the categories that you need to exclude:

Both of these filters can be true for a single row of data at the same time. This configuration will give you the intended results: a count of clients who received nutrition or health services. 

back to top

Didn't answer your question? Please email us at help@devresults.com.