I’m looking for support on how to aggregate a data element in more than one way in a pivot table. I have an aggregate data element that collects a count on a weekly basis. From this count, I would like to present this data on a monthly basis in two ways. 1) as the last reported weekly value of the month; and 2) as part of an indicator denominator where the value is summed across all weeks for the reporting period.
I’m running into difficulties because I cannot use multiple aggregation types from a single data element as it seems like I need to use both ‘sum’ and ‘last value (sum in org unit hierarchy)’ as aggregation types. Depending on which aggregation type I choose, one of the two pieces of data outline above is not correct.
Based on other community posts, I was thinking that using predictors could be a solution but they are not working for predicting values in the same period (sequential sample count = 0).
Any ideas on how to make these two different calculations? Currently on v2.33.4. Thanks!
As their name implies, predictors were designed to compute values based on past periods. But they can also compute data from current periods too! For documentation on predictors, see Home - DHIS2 Documentation. The first sentence is
A predictor tells DHIS2 how to generate a data value based on data values from past periods and/or the period of the data value.
and later on it tells you how:
Any items inside an aggregate function [just described] will be evaluated for all sampled past periods, and then combined according to the formula inside the aggregate function. Any items outside an aggregate function will be evaluated for the period in which the prediction is being made.
But maybe there is a way this could be made more emphatic in the documentation. Suggestions are always appreciated!
I’ve not tried what you want to do, but I think it should work if you have the data element to use ‘last value (sum in org unit hierarchy)’ as the aggregation type, but use predictors to create another data element that is the sum of the values during the month.
We have also realized the need for multiple aggregation types in indicators, and hope to do this in the future by allowing some syntax within an indicator expression that overrides the default aggregation type for a data element. We’ve not gotten around to this yet (always because of other priorities), but it is something we’ve thought of and would like to do.
You will need to create 2 program indicators, one with SUM and the other with Last Value. Then in pivot tables you present them side by side as either columns or rows
Thanks @Jim_Grace!! The part that was tripping me up was the generator - I missed the piece of the documentation about the generator evaluating for the current period only outside the aggregation function. It’s clear from what you quoted here there but might be helped with some examples?
Anyway, my predictors are working as expected now Thanks again for the support!
Thanks again for the support!