| Thread Previous • Date Previous • Date Next • Thread Next |
Hello All,
I have a proposed solution, but it has some caveats that have been
highlighted by Lars and Jason before, namely here
<https://lists.launchpad.net/dhis2-devs/msg30976.html> 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):
[image: 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!
Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@xxxxxxxxx
+678 5955137
On Wed, Oct 29, 2014 at 4:49 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> 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
>
>
> On Wed, Oct 29, 2014 at 1:00 AM, <buttsworthm@xxxxxxxxxxxx> 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@xxxxxxxxxxxx | 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.
>> <#1495a73bad7d6d8c_14958fd20c951905_>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help : https://help.launchpad.net/ListHelp
>
>
Attachment:
Reporting Rates.docx
Description: application/vnd.openxmlformats-officedocument.wordprocessingml.document
| Thread Previous • Date Previous • Date Next • Thread Next |
