Hello All,
I have a proposed solution, but it has some caveats that have been highlighted by Lars and Jason before, namely here and in this thread already. I’m using the word Organizational Unit below to follow the convention in DHIS2, but it is interchangeable with ‘Facilities’ or ‘Health Facilities’ or ‘Aid Posts’. There is a lot of formatting here to help with readability, so if it gets munged by Launchpad, take a look at the attachment.
Assumptions and Caveats
- Organizational units are never ‘reopened’, there is only one each of ‘openingdate’ and ‘closeddate’ so that is about all we can do
- Datasets are never removed from an organizational unit even if they don’t submit them anymore, keeps historical reporting rates accurate.
- Organizational units are responsible for submitting a completed datasets for partial time periods (E.G. Org opens on Jan 14th, they are still responsible for January’s completed dataset (if the dataset’s frequency is monthly))
The Math
Since I haven’t done Java in forever, I don’t feel qualified to even play with the source. All my math is done in Postgres, which comes with a lot of limitations, but it allows anyone with any version to use it through the ‘SQL View’ module.
Max number of Completed Datasets Expected for Opened Organizational Unit (number of timeperiods in the report’s duration, e.g. 12 for monthly frequency in yearly report)
Less Excluded timeperiods due to Opening Date
Less Excluded timeperiods due to Closing Date
Equals Expected Completed Datasets for Organizational Unit
E.G. Report is 2014 Reporting Rates for a Monthly Dataset. Organizational Unit opens February and closes in July. The math is 12(Jan to Dec) – 1(Jan) – 5(Aug to December) = 6 Expected Reports
This is run and summed with all organizational units subscribed to the dataset in question. Currently line one is already implemented inside DHIS2, so I am assuming that information is readily computable either in Java or from the Database. Lines two and three have yet to be implemented. I’m doing it this way because I can actually fit all of this math into a Postgres SELECT query without trouble, so no need for a stored procedure.
The Math in Query form
The number 12 is just because I know how many time periods are in a year for a dataset with a monthly frequency.
12 - CASE WHEN ouf.openingdate IS NULL THEN integer ‘12’
WHEN ouf.openingdate > TO_DATE(‘2015/01/01’, ‘YYYY/MM/DD’) THEN integer ‘12’
WHEN ouf.openingdate < TO_DATE(‘2014/01/01’, ‘YYYY/MM/DD’) THEN integer ‘0’
WHEN ouf.openingdate >= TO_DATE(‘2014/01/01’, ‘YYYY/MM/DD’)
THEN (CAST(TO_CHAR(ouf.openingdate, ‘MM’) AS integer) - 1)
END - CASE WHEN ouf.closeddate IS NULL THEN integer ‘0’
WHEN ouf.closeddate > TO_DATE(‘2015/01/01’, ‘YYYY/MM/DD’) THEN integer ‘0’
WHEN ouf.closeddate < TO_DATE(‘2014/01/01’, ‘YYYY/MM/DD’) THEN integer ‘12’
WHEN ouf.closeddate >= TO_DATE(‘2014/01/01’, ‘YYYY/MM/DD’)
THEN 12 - CAST(TO_CHAR(ouf.closeddate, 'MM') AS integer)
END AS expectedreps
That looks really busy to me so I will pair it down a bit.
[a] = Time periods in report duration based on dataset frequency.
[b] = Report starting date
[c] = Report ending date
[a] –
CASE
WHEN opendate IS NULL THEN [a] Should never happen, there just in case, remove all
WHEN opendate > [c] THEN [a] Opened after this report, remove all expected datasets
WHEN opendate < [b] THEN 0 Opened before this report starts, remove no datasets
**WHEN opendate >= [b] THEN opendate - 1 timeperiod Opened sometime this report, remove datasets not covered by opening date
END –
CASE
WHEN closedate IS NULL THEN 0 Not closed, remove no datasets
WHEN closedate > [c] THEN 0 Closes after this report, remove none
WHEN closedate < [b] THEN [a] Closes before this report, remove all
**WHEN closedate >= [b] THEN [a] – closedate Closed sometime in this report, remove datasets not covered by close date
END
The ** portion above is the hardest part I think. In our case it is straight forward, I just cast the month number as an integer, but what about a dataset with a weekly frequency inside a quarterly report?
X axis is report duration, y axis is dataset frequency
Daily
Weekly
Monthly
Bimonthly
Quarterly
Six-Monthly
Yearly
Daily
1
7
X
X
X
X
X
Weekly
1
X
X
X
X
X
Monthly
1
2
3
6
12
BiMonthly
1
X
3
6
Quartly
1
2
4
Six-Monthly
1
2
Yearly
1
Oranges can be completed with a date function, e.g. how many days are in February 2009?
Reds are complicated… I don’t have a solution in every case, but I had an idea to use the timeperiod table with something like the following:
SELECT COUNT(*) AS “Number of Reports”
FROM timeperiod
WHERE enddate > [org’s start date]
AND enddate < [org’s end date]
AND periodtypeid = [dataset’s period type]
Thankfully, all that is an aside in Vanuatu, because there will always be 12 expected dataset completions in a yearly report, and 1 in a monthly report. We’ve already implemented this via an SQL view here to good effect. I hope this helps somebody in the group.
Final Result
(From our test server):
![Inline image 2]()
Note that if there are future time periods, you will have to divide the result by (how far into report duration you are / total expected reports)
So for Penama Province, Monthly Health Center Report, you get 41.67 / (10 / 12) or about 50% reporting rate so far in 2014.
Going forward, it would be nice to address the issue of facilities that close and reopen. I have some ideas I’m kicking around to do this, but all will require changes to the java, gui, and database.
The full query for our implementation is included in the attached document. Cheers!
Reporting Rates.docx (58 KB)
···
Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137
On Wed, Oct 29, 2014 at 4:49 PM, Jason Pickering jason.p.pickering@gmail.com wrote:
Hi Michael,
I have seen this request in a couple of different places. Right now, it is not fixed, but it is possible to develop custom reports/analysis to take this into account. Part of the challenge is that facilities tend to open and close in some places, and in DHIS2, you only get one opening and closing date, so there is no way to record when then facility actually was open/closed over time. Of course, you could consider implementing more exotic ways of recording this with a separate dataset.
I suspect this will be fixed at some point in time in the core of DHIS2, but you may need to consider developing a custom solution to support this for now.
Regards,
Jason
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
On Wed, Oct 29, 2014 at 1:00 AM, buttsworthm@wpro.who.int wrote:
Dear Developers,
We have been implementing the DHIS2 in Vanuatu with the MoH and we are certainly pleased with the progress thus far - so thank you.
I have been searching the threads for a solution to a problem we face and came across the following:
https://lists.launchpad.net/dhis2-devs/msg30951.html
I am wondering the progress on addressing this issue – i.e. reflecting the open/closed facilities in the reporting rate summaries (and/or the pivot tables). At the moment, the system appears to calculate the rates for all open and closed facilities.
As you know, the system is only as good as the data in it – if we don’t have high coverage of facilities then we can’t rely on the data to be representative.
Calculating reporting rates was an important function that we used in our previous database because we would share these rates with managers every month to help drive the facilities to complete and return the forms.
I would be grateful if someone could tell me the status of this upgrade, when it might be completed, or if there is an alternative for calculating accurate reporting rates that I am unaware of.
Thanks & kind regards,
Michael
Michael Buttsworth
Health Information Systems Technical Officer
Office of the WHO Country Liaison Officer in Vanuatu
World Health Organization | Regional Office for the Western Pacific | Manila, Philippines
Mail address: PO Box 177, Port Vila, Vanuatu | Tel: +678 27683 ext. 83207
Mob: +678 5388052 | E-mail: buttsworthm@wpro.who.int | Web: http://www.wpro.who.int
This message was scanned for viruses with Trend Micro ScanMail, GFI MailSecurity and GFI MailEssentials by the World Health Organization Regional Office for the Western Pacific. However, the recipient is advised to scan this e-mail and any attached files for viruses.
Disclaimer:
This e-mail, together with any attachments, is intended for the named recipients only and is confidential. It may also be privileged or otherwise protected by law.
If you have received it in error, please notify the sender immediately by reply e-mail and delete it and any attachments from your system. You may not copy or disclose its contents to anyone.
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–
