When looking at indicators in pivot tables, even those orgunit/period
combinations with missing numerator is displayed (as 0%). The problem is
that these disturb the totals, as we at the current stage of
incompleteness of data submission would like to estimate the values based
on the data we actually have, not counting those months missing. Let me
illustrate with an example:
For four months, the raw data for a facility would be
4, ,5,5
So one month missing. The output numxfactor from the data mart export for
this percentage indicator would be 400, 0, 500, 500, though the missing
month is most probably NOT zero. We would like it to be excluded from the
data mart export (as is done with raw data export), since unticking
orgunits month by month in the pivot table is a rather long and tedious
process.
I think this is done in the data mart export where the numerator is
factored with the indicator type factor, always returning an integer.
Something like "null"x100=0, while in this case it makes sense to return a
"null" (which leads to not exporting the denominator too). However, this
must be an option, since many countries do not enter zeros but leave those
fields blank. Any thoughts on this?
My feeling is that if there are no values registered (in datavalue table) then there should be none in data mart either, simply leave it blank/NULL. This ‘0’ in datamart seems misleading when we know there are no values entered. I think Jason requested the same (in another mail on this list a few weeks back) based on his experience in Zambia.
best regards,
Ola Hodne Titlestad
HISP
University of Oslo
Indeed, I have essentially the same issue in Zambia, and I was only
able to accomplish this through a custom view with some stored
procedures. (See the email thread from a few weeks ago http://groups.google.com/group/dhis-users/t/1896d5a6e44d3a00\) There is
a rather long thread , along with some SQL code that I used to
construct a report. I assume it could be modified your purposes.
The other possibility would be to use a custom SELECT statement in a
reporting query (through BIRT), using LEFT OUTER JOINS to return NULL
values, but coercing them to zeros instead of NULLS in the report. Of
course there would be an obvious performance penalty if you are using
a view, but you could always materialize the view into a table,
following essentially the same procedure as a data mart export does
with Java, but relying on the database to create a reporting table. Of
course the downside of this is that there is no way in the DHIS 2
interface to refresh such a table (AFAIK). There might be better
workarounds like a scheduled pgagent task to do this job for you on a
regular basis if performance is an issue.
The queries that I describe in that thread execute fairly quickly
though, so it might be best to start with a view and then see if
materialization would be required. Of course, doing this through the
UI would probably be a better solution. Can we setup a DHIS2 hackers
group?
Regards,
JPP
···
On Tue, May 19, 2009 at 10:03 PM, Ola Hodne Titlestad <olati@ifi.uio.no> wrote:
My feeling is that if there are no values registered (in datavalue table)
then there should be none in data mart either, simply leave it blank/NULL.
This '0' in datamart seems misleading when we know there are no values
entered. I think Jason requested the same (in another mail on this list a
few weeks back) based on his experience in Zambia.
best regards,
Ola Hodne Titlestad
HISP
University of Oslo
On Tue, May 19, 2009 at 5:29 PM, <johansa@ifi.uio.no> wrote:
Another issue from Sierra Leone:
When looking at indicators in pivot tables, even those orgunit/period
combinations with missing numerator is displayed (as 0%). The problem is
that these disturb the totals, as we at the current stage of
incompleteness of data submission would like to estimate the values based
on the data we actually have, not counting those months missing. Let me
illustrate with an example:
For four months, the raw data for a facility would be
4, ,5,5
So one month missing. The output numxfactor from the data mart export for
this percentage indicator would be 400, 0, 500, 500, though the missing
month is most probably NOT zero. We would like it to be excluded from the
data mart export (as is done with raw data export), since unticking
orgunits month by month in the pivot table is a rather long and tedious
process.
I think this is done in the data mart export where the numerator is
factored with the indicator type factor, always returning an integer.
Something like "null"x100=0, while in this case it makes sense to return a
"null" (which leads to not exporting the denominator too). However, this
must be an option, since many countries do not enter zeros but leave those
fields blank. Any thoughts on this?
