Clarification around program indicators (aggregation types)

Hi,

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:

Can anyone briefly give me some practical examples of when use them? I mean one example for each aggregation type in the image

So so far I have used the commonly used ones: Sum, and count.

Thanks

Hi @fernandoshake,

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:

1 Like

Hi @Gassim,

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?

Thanks

1 Like

Following up with @tracker-analytics , thanks @fernandoshake! :smile::+1:

Hi @Gassim,

Still no news?

Thanks.

1 Like

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.

Markus

1 Like

Hi @Markus,

Below is the use case I am working to solve:

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

Thanks

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?

Best regards,
Markus

Hi @Markus,

I do not want an average for one enrollment, but I want to view the individual scores per event in the event report like this:
image

Thanks

Maybe it’s possible to run a SQL script to get these results…

Hi @Markus and @Gassim,

Could you help me with the script if it is not possible to get what I want by using event report?

To highlight that I have used the following aggregation and analytics type in the program indicator.

Looking forward to your help.

Thanks

Hi @fernandoshake

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:

  1. Program UID
  2. Program ID
  3. 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.

Markus

1 Like

Yes, @Markus! I believe this is exactly what @fernandoshake was asking! :smiley:

Thanks!