I have read the documentation about different program indicators types, but I haven’t figured out what the following highlighted aggregation types really mean in practical examples:
I think you will find this session on Last Value Aggregate Type from the DHIS2 Analytics Tools Academy - 2021 Q2 helpful. Please have a look, video URL below at the start of the topic:
I have watched the video; it deals with last value aggregation type from the perspective of a data element and it did help in that sense But ere there any videos you can share with me that talk about the same aggregation type from the perspective of program indicators?
Hey there @fernandoshake
This seems like an area where the documentation is not very extensive and could be strengthened. Did you have a specific use case you are working to solve, or are you asking for general learning purposes? If you have a specific use case it would be good if you can outline it.
I have built a tracker program with one repeatable stage in which each enrollment is for a specific health provider. The repeatable stage contains a checklist of data elements about the health provider’s work performance. This checklist can be completed multiple times. A program indicator was created to compute a summary score for each event separately.
What I want to see is a score per event in the event report to analyze the trend, but I am seeing only the score of the last event. In other words, if a health provider, for example, got the following scores (75%, 35.5%, 88% and 67%), I see only 67% in the event report, but not all individual scores.
For general learning purposes, we would also like to know more about the highlighted aggregation types.
I hope my case is clear and I am looking to a solution to my use case
Hi again @fernandoshake
Do you want to make an average for one enrollment? Your example scores 75%, 35.5%, 88% and 67% resulting in an average of 66,3%? Then you want to line list the average per person? Or do you want to then aggregate each enrollment average one more time?
I think I have the solution but as I’m looking at the DB tables and columns’ names, I see that the terminology might change a bit. Ultimately, what you are querying for is the “program stage instances” in a program stage for program instances. And so we will need to get:
Program UID
Program ID
Program stage ID
It’s possible to merge the following three queries into one, but to make it simple, we can create a new query for each:
Program UID:
You can get this one from Maintenance showing the program details
Program ID:
Create a SQL Query:
SELECT * FROM program WHERE program.Uid = ‘Replace-This-With-Program-UID’;
then copy the programid
Program stage ID
SELECT * FROM programstage WHERE programstage.Programid = ‘Replace-THIS-With-Program-ID’;
then copy the programstageid
Finally,
PogramStageInstances
SELECT * FROM programstageinstance WHERE programstageinstance.programstageid = ‘Replace-With-Program-Stage-ID’;
If the query is too big, you might need to use LIMIT and OFFSET to move across the rows (if the query is too big, it might not work without LIMIT)
SELECT * FROM programstageinstance WHERE programstageinstance.programstageid = ‘Replace-With-Program-Stage-ID’
LIMIT 5;
You can read more about managing SQL views in the docs: Manage SQL Views ¶
Hey @fernandoshake - Was @Gassim 's table showing the output you want? We would refer to this as a line list in that case. Showing individual repeatable events for one person was introduced as a feature in the new line listing app, and if you are on 2.38 or higher, you can do this through the app and API.
If you are running 2.37 or lower, a SQL solution is an alternative as @Gassim points out, but this requires some SQL knowledge to build.