Calculating the number of distinct (unique) values by categories

Use case

You have a table (a macro outputting a table) containing multiple columns with text data.

You need to calculate the number of distinct (unique) values by categories. For example, the number of unique customers in every segment.

Solution

1. Switch the page to the edit mode.
2. Insert the Table Transformer macro and paste the table within the macro body.
3. Select the macro and click Edit.
4. Switch to the SQL query tab.

5. Enter the following SQL query (select the functions depending on what you'd like to calculate):

   SELECT T1.'Segment', 
   COUNT (DISTINCT (T1.'Customer Name')) AS 'Number of customers' 
   FROM T* 
   GROUP BY T1.'Segment'

   SQL
6. Click Next.
7. Define the table settings and view options if needed.
8. Save the macro and the page.

You can use FORMATWIKI function for the purposes of cell formatting.