Spotfire: How the UniqueCount() function works when there is no grouping.

 

This article briefly describes working of the UniqueCount() function when there is no grouping. Grouping here means using UniqueCount in a bar chart, or having a tile by axis in a KPI chart, etc. When there is no grouping, UniqueCount(X) is evaluated in such a way that it lets us reuse parts of the calculation result later.

The way UniqueCount(X) is designed when there's no other grouping at all is that it calculates a full grouping for X (independent of filtering because that can be reused in a lot of situations) and then count the groups after filtering. This means that the first UniqueCount(X) call will be expensive regardless of filtering but after that it should be very fast, almost regardless of filtering (though for an expensive filter like a text filter, a lot of time is going to be spent on the filtering instead).

As soon as you have a grouping, UniqueCount is calculated per group in a different way that takes the filtering into account.

To get the unique values, UniqueCount(X) needs to compare each value with the obtained value. Also, UniqueCount(X) is a single-threaded function. So, when getting evaluated on millions of records, the CPU utilization is not going to be very high on a multicore machine. In such cases, memory utilization is needed to be looked upon.