dhis2-users team mailing list archive

[Rodolfo Melia <rmelia@xxxxxxxxxxxx>]

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.pickering@xxxxxxxxx> 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 <jimgrace@xxxxxxxxx> 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.pickering@xxxxxxxxx>
>> 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 <jimgrace@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
*Rodolfo Meliá*
*Principal*
rmelia@xxxxxxxxxxxx
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: rmelia@xxxxxxxxxxxx