I’m trying to fiugre out how to create a program indicator that will report the number of unique occurences of a text data element. For example the number of unique users id’s that performed an assessment within a give period and org unit.
Is this currently possible and if so can anybody point me in the right direction?
@gjones we would like to create a similar program indicator. We have an event program with an ID (entered as a text data value type) and want to count the total number of unique IDs entered rather than each event.
One way we are thinking about getting around this is to switch to a tracker program so that we can count enrollments (which would be unique according to the ID mentioned above). However, we are not sure we need the complexity of a tracker program for any other reason than to produce this indicator.
@Markus do you think this request could be related to this JIRA request [DHIS2-5934] - Jira) ?
This seems it would work - if you switch to a tracker program.
You could also try to use custom aggregation for the program indicator, to count distinct values.
Aggregation type: Custom
Expression: COUNT(DISTINCT #{your.data.element})
(Edited 2019.01.21)
Custom aggregation types are not supported to view in the tracked entity instance dashboard however, only as calculated indicators in the aggregate apps.
Markus
Thanks @Markus. We’ve tried the syntax for a custom aggregation for a program indicator that you shared and we were not able to get it to work. Does this only work on certain versions? We are on 2.31.4
Thanks for letting us know. Custom aggregation types is supported in 2.31, but working with it there is a lot of freedom on the part of the kinds of expressions you can build - and there might be undocumented limitations. Could you send all the details on your program indicator?
@Markus we tried a couple different syntax in the expression:
COUNT DISTINCT(#{dataElement})count distinct(#{dataElement})count(distinct(#{dataElement}))For all, the UI said that the ‘expression is not valid’.
Otherwise, no filter was added. Aggregation type was set to custom and analytics type set to event.
Thanks @Kris_Reinhardt,
I was expecting one of these to work:
Did you also try and run the program indicator in pivot tables? In such advanced cases it might be that the expression validation is a bit stricter than it should be - and it might be that the expression actually would work in pivot.
Sorry @Kris_Reinhardt, I had sent you the wrong syntax above.
This should work:
count(distinct #{dataElement})
Verified on a 2.31 environment, with an event type program indicator and a data element.
Best,
Markus
thanks!! It’s working in our system as well.
@Markus
We are trying to set up the same program indicator in 2.32, and it doesn’t seem to work.
The data element is a userID of type text.
The program indicator has:
Aggregation type: Custom
Analytics type: Event
Expression: count(distinct #{dataElement})
When I try to add this program indicator as data in pivot tables, I get the following error:
"
Code: 409
Status: Conflict
extraneous input ‘distinct’ expecting …
"
The program indicator also says the expression is not valid. Any tips?
Thanks @Markus got expression error for version 2.31.5 but it works nicely while placed it in pivot table.
I can’t seem this to get to work on 2.31 or 2.35 (haven’t tried other set-ups)
In Play, 2.35.0:
Program Indicator for Antenatal Care Visit
count(distinct #{dBwrot7S420.vANAXwtLwcT})
DE is WHOMCH Hemoglobin value
Pivot Table Error Notification:
Code: 409
Status: Conflictextraneous input ‘distinct’ expecting {‘(’, ‘+’, ‘-’, ‘not’, ‘!’, ‘firstNonNull(’, ‘greatest(’, ‘if(’, ‘isNotNull(’, ‘isNull(’, ‘least(’, ‘log(’, ‘log10(’, ‘avg(’, ‘count(’, ‘max(’, ‘median(’, ‘min(’, ‘percentileCont(’, ‘stddev(’, ‘stddevPop(’, ‘stddevSamp(’, ‘sum(’, ‘variance(’, ‘d2:addDays(’, ‘d2:ceil(’, ‘d2:concatenate(’, ‘d2:condition(’, ‘d2:count(’, ‘d2:countIfCondition(’, ‘d2:countIfValue(’, ‘d2:countIfZeroPos(’, ‘d2:daysBetween(’, ‘d2:floor(’, ‘d2:hasUserRole(’, ‘d2:hasValue(’, ‘d2:inOrgUnitGroup(’, ‘d2:lastEventDate(’, ‘d2:left(’, ‘d2:length(’, ‘d2:maxValue(’, ‘d2:minutesBetween(’, ‘d2:minValue(’, ‘d2:modulus(’, ‘d2:monthsBetween(’, ‘d2:oizp(’, ‘d2:relationshipCount(’, ‘d2:right(’, ‘d2:round(’, ‘d2:split(’, ‘d2:substring(’, ‘d2:validatePattern(’, ‘d2:weeksBetween(’, ‘d2:yearsBetween(’, ‘d2:zing(’, ‘d2:zpvc(’, ‘d2:zScoreHFA(’, ‘d2:zScoreWFA(’, ‘d2:zScoreWFH(’, ‘#{’, ‘A{’, ‘C{’, ‘D{’, ‘I{’, ‘N{’, ‘OUG{’, ‘PS_EVENTDATE:’, ‘R{’, ‘V{’, ‘[days]’, INTEGER_LITERAL, NUMERIC_LITERAL, BOOLEAN_LITERAL, QUOTED_UID, STRING_LITERAL, WS} at character 6.
In 2.31.10 I don’t get an error, but also don’t get any values to actually populate. Is the count(distinct #{dataElement}) limited to specific (text, perhaps) formats?
@Markus a little confused; seeing your response here Need help - Using count distinct clause to eliminate repetitive data - #12 by Markus, however this doesn’t seem to work for later versions (see above).
Thanks @Matthew_Boddie - It does indeed seem like a bug. You can follow the ticket here: [DHIS2-11327] - Jira
Thank you so much @Kris_Reinhardt for raising this query and following up and thank you so much @Markus for providing the solution. It is working perfectly on my instance.
Thank you so much @Kris_Reinhardt for raising this query and following up and thank you so much @Markus for providing the solution. It is working perfectly on my instance.
) but the PI is appearing as blank in pivot tables.