dhis2-devs team mailing list archive

["Laura E. Lincks" <laura.lincks@xxxxxxxxxxxxxxxxx>]

Jason, these are excellent suggestions. Thank you. And you're right about
the "A" having priority... but what if "B" is better? I asked these same
questions of our staff yesterday. We are continuing to learn exactly what
is needed. Injecting the result in a non-editable data element is an
interesting idea... however we couldn't combine all necessary "indicators"
(consisting of simple and complex indicators, the complex ones being actual
data elements) when creating a pivot table. I guess we'd have to separate
the simple ones from the complex ones to get a complete output.

I think there is a possibility of structuring a sort of IF-THEN-ELSE-like
structure by evaluating sums and multiplying or dividing them by other data
elements to result in a 0 or 1 (a false or a true) and then proceeding
accordingly with that result. It would be messy, but could possibly be done
in native DHIS ... but only if we are analyzing every data element at the
same hierarchical level (which is a big IF in our case and one we are
researching more).

For example we created another formula at the facility level that
translates to:
​Return TRUE if ​a facility's
TB Screening / Current In care
​>
 80%
​AND its
On CTX / Current in Care
​>
 80%
​, Otherwise return FALSE​

IF the SUM of the (SUM of Data Element Group A) DIVIDED BY the (SUM of Data
Element Group B) IS GREATER THAN 80%
   AND IF the SUM of the (SUM of Data Element Group C) DIVIDED BY the (SUM
of Data Element Group B) IS GREATER THAN 80%
THEN
   RETURN 1
ELSE
   RETURN 0

The formula is as follows:

​

(#{ccDP1mdtsGw.WiRMti12cez}+#{LMg3WmDU9e0.WiRMti12cez}+#{oZ1lP3c6aUy.WiRMti12cez}+#{vIYHoB1chrN.WiRMti12cez})/
(#{uRhTwUUG8Xc.WiRMti12cez}+#{m1yNfb8Pk5o.WiRMti12cez}+#{Sll1gd58DuW.WiRMti12cez}+#{q6Ifdhfa9UZ.WiRMti12cez})>.8

AND
(#{Y6x3Qc60cny.WiRMti12cez}+#{KWfnVyJ6csJ.WiRMti12cez}+#{C8xQ25DfJ4c.WiRMti12cez}+#{niVwX6llpwW.WiRMti12cez})/(#{uRhTwUUG8Xc.WiRMti12cez}+#{m1yNfb8Pk5o.WiRMti12cez}+#{Sll1gd58DuW.WiRMti12cez}+#{q6Ifdhfa9UZ.WiRMti12cez})>.8


Something like that could be applied to yet another sum of data elements,
in our case to come up with the value we need. Again, messy, but possibly
doable without making an external call.

I agree that making an external call to do the calculations is probably the
best solution ultimately if not the only solution, depending on at what
level we decide to store the data in question.

Thanks for your examples. We'll definitely study them further along with
the new Exclusive Pair operator.


*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

On Tue, Mar 22, 2016 at 3:55 AM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

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