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