Creation of indicator based on Monthly data and aggregating to Quarterly with some logic for quarterly aggregation and a different one for geographical aggregation

Dear all,

We are facing some issues trying to make some specific configuration work and we were wondering if, maybe, we are trying to do something that is not feasible in DHIS2. I explain the issue

We have to create an indicator based on a yes/no monthly entered DE that have to follow the following logic:

  • It shows at facility level 0 or 100 depending on that yes/no DE
  • If any of the months in the quarter is no (0), then our indicator evaluated at quarterly level should be no (0)
  • When evaluating at district level, that indicator should show the percentage of availability, but based on the quarterly aggregation.

In a nutshell what we want is and indicator doing this:
Quarter 1 Month 1 Month 2 Month 3
District 50 100 50 100
Facility 1 100 100 100 100
Facility 2 0 100 0 100

up to date we have been trying and we are able to get:

  • Either some indicator that works at facility level properly but do not aggregate properly to the district level
  • Either some indicator that aggregates well at district level but do not take into account the logic of “all months must be 100 for a quarter to be considered 100”

What this indicator is trying to show is availability of a certain drug, and a drug is considered available at quarterly level only if all months say available.

We have tried these different strategies without success until now:

  • Using indicators if if() functions to write 0s when the indicator is not 100. But this doesn’t work when aggregating to district as it shows always 0
  • Playing with the aggregation type of the DE, but we don’t see what we need (aggregate as min in the temporal dimension and as sum in the geographical dimension)
  • Using Predictors to read monthly data and write a quarterly DE to be used for the evaluation of the indicator at quarterly level (this doesn’t seem to work as it looks like reading at district level and writting at quarterly period is not working, and likewise Predictors don’t seem to be able to base their generator operation in indicators, but in the base raw DVs of the DE

Does anyone have any idea about how to solve this?

Thank you very much in advance

Hi @ifoche

You’ll have to use predictors for this because you need to define the org unit level at which to run the analysis in order to produce the desired value.

I would make one predictor that runs at facility level which is set to monthly. The expression would be if(DEUID == ‘yes’, 100,0). Your sequential, annual, and skip count will all be 0

Then you need to make a second predictor to run at district level to evaluate average of the the facility level predictor. That expression would be avg(Predictor 1 DEUID). Again the counts are set to 0.

Then you need to schedule your first predictor to run before your second predictor and your analytics tables to run after that. Finally, you could just make an indicator that contains only those data elements so that your end users still find it as an indicator if that is what they are expecting.

Hope this help. Let me know.

Thank you so much @Scott ! I’ve been testing though and at least in 2.38.4.3 there seem to be some limitations/issues to implement your recommendation:

  1. On the first Predictor I had to slightly modify it for it to work, when using ==“yes” that was not working so I had to use ==1, which worked like a charm and then I had some meaningful values…
    screenshots_1695388158_1509_22092023_407x198

  2. However, the second predictor is giving me 0 predictions when running. If I got it properly:

I don’t get any prediction for this second predictor… any thoughts on how that could be possible? Should I test in other newer versions of DHIS2?

Thank you so much for your help!

Just for the Record, in case someone from the community lands here with the same need.

This was indeed not possible with the current implementation of predictors and indicators. Predictor couldn’t make it to use analytics, they relay on raw data, so cannot aggregate up, while indicators, even using subexpressions, where not doing the job either, as booleans were not being able to participate of aggregations (@Jim_Grace would have more details on it).

Thanks to @Scott and @Jim_Grace that drove all the process of identifying the problem and solution. Finally the way to implement these type of indicators, that will be available from 40.2 on, will be by using indicators with subexpressions, once they will work properly in 40.2. So in the end a single indicator like this…

Indicator type: Per cent
Numerator: subExpression(if(#{KwigVroDOBw}.aggregationType(SUM)==3,1,0))
Denominator: R{a0egEq3UhL1.EXPECTED_REPORTS}

…should do the job.

1 Like