aggr function returning null.

[yh2023]

Hi everyone,

When attempting to retrieve the distinct count of 'app_yhno' across Splty a few values are null.

count(distinct app_yhno) gives counts across all dimensions, whereas applying the aggregation function aggr(count(distinct app_yhno), Splty) only produces values randomly and returns null value.

Specifically, the aim is to obtain a value of 18 for all Anaesthesia in the provided image.

Any help is greatly appreciated.

[LRuCelver · Accepted solution]

Try this instead:

Count(distinct total <Splty> app_yhno)

[rwunderlich]

Aggr is distinct by default. So you need to add NoDistinct as an Aggr parameter. 

aggr(NoDistinct count(distinct app_yhno), Splty)

However, as @LRuCelver suggested, Total<> is probably better than Aggr in this case.

-Rob

[yh2023]

Thank you it works!!