Hi - I also want to run this kind of calculations - at the moment, for my demos, all I have is a SQL that updates/ insert values on DataElements that I've created just to contain this calculated values. I do this to move a stock closing balance as opening balance for the next month, calculate average distribution for the last 12 months, and other requirements that need if statements, something that I cannot do within DHIS.
I read that you use R for doing this, which I'm not familiar with. If you just want to schedule SQL task to run every 24 hours that insert/update data values, what's the best approach? What 'scheduler' can I install on my server, so this SQL statements are run nightly? R On Thu, May 16, 2013 at 2: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 > > -- *Rodolfo Meliá* *Principal* rme...@knowming.com www.knowming.com +1 708 872 7636 +44 777 576 4090 Skype: rod.melia Twitter: @RodolfoMelia <https://twitter.com/RodolfoMelia> Note: Please note my new email address, which I will be using for PSI related work: rme...@knowming.com
_______________________________________________ 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
