Hi Jason, Thanks, that sounds like the best option for us. I had almost gotten there myself. I've just been describing to our M&E folks the various suggestions I've received (thanks by the way Knut for the HTML report idea!) They are having a hard time understanding why something so easy in Excel isn't supported more simply through DHIS. I'm speculating that it may be because HIV/AIDS treatment and care is a relatively new service, so people may be more interested in cumulative numbers in this area; nobody is asking how many deliveries have been done in a hospital since it was founded 100 years ago!
I was thinking as a last resort we could define an extra data element but don't put it in one of the entry datasets. Then through the Web API I could periodically compute it for each site/month and upload it. But your approach sounds more efficient. Short of plunging in and reading the source code (which maybe I should do anyway), how do I understand what to do? Do I create a dummy indicator? How is the aggregateddatavalue table used? (Is it still used now that we have dynamic aggregation?) If you give me some initial help I may be able to find my way from there. Cheers, Jim On May 16, 2013, at 4:07 PM, Jason Pickering <jason.p.picker...@gmail.com> wrote: > Hi Jim, > > Personally, I think the easiest way would be an external script which would > process the data and then inject it back into the aggregateddatavalue table. > Some might call this a "hack", but we have used this approach for > calculation of these types of cumulative numbers and it seems to work fine. > In our case, we use a R script to make the calculation, and then inject the > data back into the aggregateddatavalue table. The script is executed on a > nightly basis. > > The advantage with this approach is that the cumulative numbers are then > available through the normal tools to the user, such as the charts, and of > course, could be pulled out quite easily with the WebAPI into this Access > tool. > > I am sure there are other approaches, but this is one of them. :) > > Regards, > Jason > > > > > > On Thu, May 16, 2013 at 2:47 PM, Jim Grace <jimgr...@gmail.com> wrote: > Hi Jason, > > Thanks. I agree it's best not to collect a cumulative amount as a data > element when it could be derived. You've given me some ideas how to pull it > out -- for instance I see I can write a Jasper report based on a query. > Although otherwise I was thinking we could pull all the data we need through > Report Tables -- which would be easier for FACES to maintain after I leave. > As a possible future DHIS enhancement, I think some way of getting this > functionality in a Report Table would be preferable, whether as a report > table feature, or as a way of defining a cumulative indicator (even better in > my opinion, because of the many ways that indicators can be used.) I realize > it would be performance expensive to compute such an indicator. But if it's > what you need, you have to pay this price one way or another. > > At the moment I'm writing some code to use the Web API to pull out the FACES > data and put it into the MS Access reporting tool. Kenya is set to convert > from this tool to DHIS, but meanwhile we have to report through it. So it > sounds like for each site/month I will have to pull this data element for all > previous months and sum it in my tool. Or could I somehow get this through a > SQL view? I don't see any way of using parameters in a SQL view. (Am I > missing something?) So would I have to write a SQL view to generate the > cumulatives for all possible reporting months for all FACES sites? That > sounds awkward. Maybe pulling all prior months through the API is the lesser > evil, even though the Internet connection is somewhat slow here in Kenya. > > Cheers, > Jim > > On May 16, 2013, at 2:26 PM, Jason Pickering <jason.p.picker...@gmail.com> > wrote: > >> Hi Jim, >> I have seen this exact same data element being collected USAID/PEPFAR >> supported organisations in Nigeria, Different organisations there, follow >> different approaches, either of collecting it in the way which you mention, >> or recording the cumulative figure each month. In the context of DHIS2, the >> recording of cumulative totals is not really a great idea, because there is >> not a "LATEST" aggregation operator, whereby the system simply would take >> the latest available cumulative figure as the current one. >> >> With that in mind, it is number better to simply record the number of new >> entrants each month. Once you have this, you can easily create a custom >> report to accumulate the data from inception, use an SQL query to aggregate >> it directly, or pull it out into other analytical tools such as R/Stata. I >> think if you need something for end-users, you would need to develop a >> custom report to achieve this, whereby data would be aggregated from >> inception of reporting, up until the "End date" chosen by the user. >> >> Best regards, >> Jason >> >> >> >> >> On Thu, May 16, 2013 at 10:58 AM, Jim Grace <jimgr...@gmail.com> wrote: >> Hi All, >> >> My PEPFAR partner organization FACES in Kenya is configuring our own DHIS2 >> instance to collect data among the clinics we support. One of the standard >> government variables we collect and report each month is "ever enrolled in >> [HIV/AIDS] care". This means the cumulative number enrolled since the start >> of the service at each facility, which is often several years ago. It equals >> the sum of all the "enrolled this month" numbers going back through time. >> For example if "ever enrolled" is 4000 in January 2013, and "enrolled this >> month" is 100 in February 2013, then "ever enrolled" must be 4100 in >> February. >> >> I am not (yet) seeing a good way to compute this in DHIS, so I'm asking >> advice. When we first started collecting this data from clinics in our >> current system of spreadsheets, we asked each clinic to compute the >> cumulative total and enter it for each month. We had a lot of errors this >> way, and we realized that it would be better calculating this in our tool >> instead of asking the data clerks to do this. So in our spreadsheets we just >> compute it by taking the previous month's "ever enrolled" and adding this >> month's "enrolled this month". Our records don't always go back to the start >> of care at each clinic, so we usually have an initial, hand-entered "ever >> enrolled" to get things started on the month before we enter real data for >> that clinic. >> >> Now we are trying to convert our system of spreadsheets to DHIS, and I don't >> see a good way to do this. I've tried creating a Report Table with "Include >> cumulative" checked, but it only gives me cumulative numbers within the >> range of report months. What we really need is to get the numbers for one or >> more sites, for one or more months, and to have "ever enrolled" as one of >> the numbers that is reported per site, per month. The best options I can >> think of are: >> >> 1. Ask each site to compute this number each month and enter it as a data >> element. >> >> 2. Export the data from DHIS into our own software and have our software >> compute this number. >> >> Are there any better options? >> >> Cheers, >> Jim >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~dhis2-users >> Post to : dhis2-users@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~dhis2-users >> More help : https://help.launchpad.net/ListHelp >> >> > >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
