Counting number of distinct/unique values

[Anna Fergusson]

Kia ora

Am I missing a function that would allow me to count the number of distinct or unique values when I do a summary-type calculation? I can do count(value = "dog") but it would be good to be able to do something like count(unique(value)).

My workaround is to do this:
if(count(value = "dog") >= 1, 1, 0) + if(count(value = "cat") >= 1, 1, 0) + if(count(value = "rabbit") >= 1, 1, 0) but I’m not a huge fan of this approach.

Is there a better way?

🙂 Anna

[Bill Finzer]

Hi Anna,

Yes, it would be very nice to have a function “countUnique” or some such that returned the number of unique values of an attribute. I’ll add this to the feature request list!

I understand why you might not be a fan of your workaround. 😉 Another workaround you may have thought of is to group by value by moving the value attribute all the way to the left. Now, of course, the count of unique values is the number of rows at that level. If you want a computed count of unique values, you can define an attribute one level higher that counts those rows.

The enclosed screenshot shows the situation using the Roller Coasters example doc.

Bill

• This reply was modified 5 years, 2 months ago by Bill Finzer.

Attachments:

1. 

   count-unique.png
   

[Anna Fergusson]

Thanks Bill 🙂

For what I want to do specifically, the restructuring approach doesn’t help but I think it’s a nice way to visualise the idea that could help my students understand a function like unique() when they see it.

Ngā mihi
Anna

[Anna Fergusson]

Kia ora Bill

I returned to this project and realised that there is now uniqueValues() function. Awesome 🙂

Anna

[Author]

Posts