dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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
>>
>>
>
>