Charts on distinct values

EWG IT Corporate
EWG IT Corporate Member
in Charting

I have a dataset, which is the result of a join so the ID is not a unique key:

+————+——————+——————-+—————|
| — ID — | — Name — | — Gender — | — Role — |
+————+——————+——————-+—————|
| 001 | Alex B | Male | User |
| 001 | Alex B | Male | Admin |
| 002 | John C | Male | Agent |
| 003 | Drew H | Female | User |
| 003 | Drew H | Female | Agent |
+————+——————+——————-+—————|

I know how to use count distinct with CASE to get the right numbers on Single Value charts, but I would like to find a way to build other charts counting ID just once.

For instance, if I build a Pie Chart on Gender (and Count of Gender), I will get this:
Male: 3
Female: 2

But I would like to get this instead:
Male: 2
Female: 1

How can I get this?

Thanks,
Gianfranco

Best Answer

  • david_cunningham
    david_cunningham Coach
    edited April 2024 Answer ✓

    You can do COUNT(DISTINCT id) @EWG IT Corporate

    Your example data

    image.png

    Beast Mode

    image.png

    Result

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach
    edited April 2024

    COUNT(DISTINCT Gender)

    You don't have to have it in a case statement.

    If this answers your question, please 'like' and 'accept' it as the answer 😁

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • EWG IT Corporate
    EWG IT Corporate Member

    Hi David,

    thanks for your feedback. The issue is not counting distinct genders but counting distinct IDs.

    If I apply your solution, I get this:

    Male: 1
    Female: 1

  • david_cunningham
    david_cunningham Coach
    edited April 2024 Answer ✓

    You can do COUNT(DISTINCT id) @EWG IT Corporate

    Your example data

    image.png

    Beast Mode

    image.png

    Result

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • EWG IT Corporate
    EWG IT Corporate Member

    This one works!

    Thanks a lot.

Categories