Hello community,
I am looking for the best way to create an indicator that counts the number of events that fall between a certain date, in this case the art_start_date (not necessarily the event date) i.e., between April and June 2023. How can I write a filter expression to calculate this?
Regards,
Andrew
When creating the program indicator there is the expression and the filter, so in the expression you will count the event using the event name and in the filter you will make the condition that event date is between April and June 2023.
I think filter will be something similar to:
d2:condition(‘V{event_date} > “2023-04-01”’,d2:condition(‘V{event_date} < “2023-06-30”’,true,false),false)
However, we’re using a specific date here and it’d have been better if the dates were from a data element for instance so that they are not static hence there’ll be no need to create an indicator for each number of months.
I hope you find the following links to the documentation helpful:
https://docs.dhis2.org/en/use/user-guides/dhis-core-version-master/configuring-the-system/programs.html#reference_information_event_program_notification_parameters
Thanks!
Hi @Gassim thanks for the feedback. I am getting an “Expression invalid” error. Invalid string token ‘syntax error’ at line:1 character:13
Apologies, copy and paste didn’t work because I put the expression as a quotation instead of code
d2:condition('V{event_date} > "2023-04-01"',d2:condition('V{event_date} < "2023-06-30"',true,false),false) the single quotes changes from ' to ‘
Hi @Gassim thanks for the feedback, this one passed the validation check. However, when I run the pivot table with this, I get an error message of “No data available”, yet the data is available for this indicator for that period of time.
Please did you try to run the Analytics Tables export and clear cache? Thanks!
Hi @Gassim, Yes, I did all that, and this is the error that I see.
It looks like a database-related issue and I can’t seem to find a proper solution for now.
Regards,
Andrew
Hi @Andrew_Kabala ,
I think I see a few things that may be up here…
First, a database related issue. The error “cannot execute readval() in a read only transaction” is also found here Data Visualizer - SQLState: 25006 Error: cannot execute nextval() in a read-only transaction
For this it would be helpful to know which version of DHIS2 you are using, so if it is related to this bug it can be added to the ticket.
Second and most importantly, I see a possible misalignment between the query you want to make and the PI configuration.
Does this sound like a correct reframing of your requirement?
“I want to count the number of events where the value for data element ART start date falls between the start of the period and the end of the period. An example period I would request in Data Visualizer is the second quarter of 2023”
If that is the case, you can use Custom Analytics Period boundaries in the PI configuration.
Analytics Type: Event
Aggregation Type: Count
Expression: V{event_count}
Filter = true
Boundary target = Custom
Custom boundary text = #{programStageUID.ARTStartDateDataElementUID}
Analytics period boundary date = After start of reporting period
Repeat the above boundary wirth analytics period boundary = Before end of reporting period
If for some reason you want to remove enrollments that started after the end of the reporting period (ie after June) where the ART start date was within the reporting period (Apr -June) you could add another analytics period boundary for Enrollment date before end of reporting period.
See here for more details
