Count Unique Sets Examples

Count Unique Sets is a useful calculation tool to count unique data table rows based on user-defined criteria. This help page walks through a few practical count unique sets examples. 

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 8. There are 8 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
  • luke@devresults.com / Launching & Landing
  • luke@devresults.com / Swordsmanship
  • leia@devresults.com / Launching & Landing

Configure a Count Unique Sets Indicator

For information on how to map indicators to data tables, see Data Source: Populate from a Data Table. You can also follow the instructions on how to configure Count Unique Sets on that same page. 

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  to determine uniqueness:

Note: if your indicator is set to report at any Geographic Disaggregation 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 geographic disaggregation 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 the reporting period that 03 May 2021, the most recent date in the Date column falls in. Count Unique for both the Trainee and Training Type column would give you the result of 6 as of 03 May 2021:

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

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 8:

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

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. Filters can use one of four operators: = (equal-to), ≠ (not equal-to), has a value (not null), or has no value (null).

If we wanted to show how many unique trainees had completed the swordsmanship training in each reporting period, the total would be 2, and both entries would be associated with the 3 Jan 2021. 

Troubleshooting Tips for Count Unique Columns

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