I have a problematic case with aggregating indicator values. The orgunit hierarchy aggregation returns incorrect values when the aggregation is total totalled up. It would have been so much easier if the Pivot Table had a conditional dimension as we have in Event Report where you can specify aggregation for data elements with specific values. In this case I have 4 data elements (e.g. ValueA, B, C and D). However I am only interested in the aggregate of these values if they meet a particular condition. For instance for each Orgunit, The data elements are summed up and multiplied by the condition (0 or 1) to return a subset of aggregated values. I have tried an illustration in the table attached. If I list all the Health facilities and use the Pivot Table Column Total, I get the correct values. However, with so many health facilities, I need these values higher up but the total I get is the Health Facility Value total without any regard for the condition. I have tried to use groups and different formula combinations but no joy.
It is possible that the problem is setting the correct formula or maybe something to do with DHIS2 aggregation. It is unlikely that I am alone in requiring this function.
Any input would be most appreciated.