dhis2-users team mailing list archive

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

Hi Larua,

As Greg says, DHIS2 is fairly limited when it comes to performing these
types of complex operations in indicators. There are a number of different
approaches, but one which I have used fairly often is to perform the type
of operation Greg mentions to calculate some indicator values, or perhaps
some intermediate values which then need to be processed. This data can
then be passed to something like R or Python (or really what have you) to
perform perhaps additional calculations, and then inject this back into the
system (via the API as a new data element) which can be processed by DHIS2.

Your case is not one I have heard of before so I think you would have to
write some custom code to accomplish this. Likely, what I would do would be
to implemnent this logic as an SQL function and then import the processed
value back into the system as a new data element (which would never be
entered directly, but rather simply calculated by your custom code).

Its not ideal and can be rather brittle, but with a generic piece of
software like DHIS2, it can be tough to support all of these special
requirements which may exist.

Another option which you may want to consider, if it is an option, is the
use of the "Exclusive pair" operator which we recently added. In your case,
it would seem to be somewhat problematic if you have all three data
elements. It seems "A" has priority, but what happens if "B" is "better"
somehow? It would seem to be more efficient and increase data quality if
you only ever had A, B or C and not all three of them, unless of course
these are somehow used in different calculations. Anyway, just maybe
something to consider. And of course, obviously, it is currently not
possible to look at relationships between a facility and a sub-facility in
a validation rule, so that's a bit of a limitation.
sta
Although not directly related to this case, here
<https://github.com/dhis2/datim-dedupe/tree/master/sql/app>is an example of
a procedural SQL function which is used for a DHIS2 app, and shows some of
the concepts which are described in the user manual
<http://dhis2.github.io/dhis2-docs/2.21/en/developer/html/ch01s25.html> in
regards to using paramaterized SQL views/functions with DHIS2. This might
help to get you started.

Regards,
Jason


On Mon, Mar 21, 2016 at 9:08 PM, Greg Rowles <greg.rowles@xxxxxxxxx> wrote:

> Hi Laura
>
> It sounds like you need an ETL developer to write custom scripts. We were
> required to do similar aggregations for our MomConnect project in South
> Africa. At the end of the day (with guidance from Jason Pickering) we wrote
> a single (complex) script which was saved as a postgreSQL function inside
> the DHIS2 database. Our function was referenced in a custom SQLview (in
> DHIS2) and with help from Pierre Dane the SQLview was scheduled to run
> right before the analytics process. Unfortunately it's not possible to
> create this type of solution through the interface but we'll happily assist
> where possible...
>
> Kind regards,
> Greg
> On 21 Mar 2016 6:48 PM, "Laura E. Lincks" <laura.lincks@xxxxxxxxxxxxxxxxx>
> wrote:
>
>> Hello, all,
>>
>> I am posing a general question to the groups which does not necessarily
>> pertain to any specific version of DHIS.
>>
>> We have a need to perform rather complex calculations to create
>> indicators. One current need of ours can be summarized as follows:
>>
>> IF facility data exists for this list (A) of data elements, sum these (A)
>> data elements
>> ELSE
>> IF facility data exists for this list (B) of data elements, sum these (B)
>> data elements
>> ELSE
>> IF facility data exists for this list (C) of data elements, sum these (C)
>> data elements
>> ELSE
>> sum these (D) data elements captured at the SUB-facility level
>>
>> We don't think native DHIS can accommodate the above example. (If it can,
>> please enlighten me.) We are exploring alternatives that can function with
>> DHIS, such as calling and external application from DHIS to perform the
>> calculation and returning the result to DHIS for reporting needs.
>>
>> I imagine we are not alone in needing more robust formula creation and
>> was hoping the members of the DHIS community could share with us their
>> solutions. Has anyone incorporated a work around for complex formulas to
>> create indicators or validation rules?
>>
>> Thanks in advance for your input.
>>
>> *Laura E. Lincks*
>> Database Manager/Developer
>> ICAP - Columbia University
>> Mailman School of Public Health
>> 60 Haven Ave, Floor B1
>> New York, NY 10032
>> Tel: 212 304 7132
>>
>> _______________________________________________
>> 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
>>
>>
> _______________________________________________
> 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
>
>


-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049