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.
···
On Tue, Mar 22, 2016 at 3:55 AM, Jason Pickering jason.p.pickering@gmail.com 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 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 in regards to using paramaterized SQL views/functions with DHIS2. This might help to get you started.
Regards,
Jason
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 Mon, Mar 21, 2016 at 9:08 PM, Greg Rowles greg.rowles@gmail.com 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@icap.columbia.edu 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
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
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
–