Data Table Variations: Count Unique Sets

This tool lets you select the column for which you'd like to count the unique values. 

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

For basic information about mapping indicators to data tables, first see Data Source: Populate from a Data Table. To create a Count Unique 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.

Also, if you are not using the top-level geography for your site (such as the world or country) as your reporting level, you will also need to include 

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

Adding Filters

When using Count Unique, you can add filters as usual. 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 (not null), or has no value (null).

In this case, the indicator will pull data from the data table only where the Train the Trainers Training Completed? column equals Yes.

For the data table above, this mapping would give you a result of 4. There are 4 unique pairs of pairs of values in the Trainee and Training Type columns where the Training Type column is set to "Swordsmanship".

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.
Didn't answer your question? Please email us at help@devresults.com.