Need help - Using count distinct clause to eliminate repetitive data

Hi all,

I have created an event program where one of the data elements is beneficiary code. The beneficiary code is a text data type. My use case says the same code can be entered several times and what I want is to create a program indicator to eliminate the repetitive appearance of the same data. Like using DISTINCT clause to count the code only once.

e.g.
image

From the above example, I should get 8, not 12 as a result because the beneficiary code is repeated in some cells.

I am wondering if any one form the community help me create the program indicator.

Thanks

hi @fernando , you might want to follow (and upvote) this jira issue 5934 as distinct count d2 functions are not available (yet).

Hey guys - you can have a look at this thread - using custom aggregation type to count distinct values:
https://community.dhis2.org/t/program-indicator-for-count-of-unique-distinct-values/37946/9

Hi @dhuser,

I have voted the issue.

Thanks

Hi @Markus,

I can not access the link. I get the following message: ‘The page doesn’t exist or is private.’

Thanks

H @fernando. seems like the topic was deleted by accident, I undeleted it. Can you see the topic now?

Hi @Karoline,

I used the following syntax as suggested by @Markus, but it is not working:

count(distinct #{dataElement})

Thanks

Hi @fernando,
How are you? I see you edited the post from December, are you still facing an issue? Please let me know so I follow up with this.

@dhis2-tracker team would you check this post please.

Hi @Gassim,

Yes, I am still facing the issue. I never got the solution.

Thanks

Hi @Gassim,

I am facing this issue too. using DHIS2 2.31.7.
PI:

  1. Expression

  2. Aggregate type
    image

My objective, need to count distinct data on program stage. But when go to pivot table, got nothing.

Thanks.

Ditto @fernando and @LCaynar in 2.35.3, distinct count is not functional. Definitely agree that this would be very valuable.

Hi there @LCaynar and @Matthew_Boddie ,
There was a slight mixup in the expression syntax above. When using te “CUSTOM” aggregation type, you would have to insert the aggregation keyword around the expression like this:

count(distinct #{dataElement})

1 Like

I have tried this and commented in the post Program indicator for count of unique/distinct values - #17 by Matthew_Boddie (as have others) on this not being functional. Though perhaps we are doing something incorrect?

Thanks - It does indeed seem like a bug. You can follow the ticket here: https://jira.dhis2.org/browse/DHIS2-11327

1 Like

Came back to this and seeing now that this is done as of a few months ago! Excited for future patches that will include the distinct count fix.

CC @lorimetz4 @jillianberkowitz @chase.freeman @megankill @dhuser

@Matthew_Boddie thanks, isn’t it already available in 2.38.0 and the latest releases? Or do you mean when you update/upgrade your instance/s?

Thanks!

2.36.11, 2.37.7, and 2.35.14 aren’t released yet to my knowledge, which is where the distinct count is noted to be effective-- right @Gassim ?

1 Like

Thanks Matthew! From Jim Grace’s comments in the Jira issue you shared, it has been back ported to 2.36.10 (released) and 2.37.5 (2.37.6 is the latest release). And, yes, 2.35.14 hasn’t been released yet so if you’re on 2.35, you’re right you’d need to wait. :pray: