dhis2-users team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Hi Randy,

thanks for the input.


On Fri, Apr 4, 2014 at 10:28 AM, Wilson,Randy <rwilson@xxxxxxx> wrote:

>  Hi Lars,
>
>
>
> I was thinking an app might be a good solution as well in the medium term,
> but ideally we could build this into the set of tools available in the
> indicator definition toolbox.  Other useful indicator functions would be:
>

Yes. I don't really see how to build that into the indicator formulas in an
elegant way at the moment, but lets think more about it later.


>
>
> IF or CASE statements - useful for recoding variables: IF('Patients on
> ART'>=1,1,0) - a quick way to define a yes/no variable to show if a service
> is provided
>

Okay that would be feasible, given the support for if/case in
postgres/mysql SQL.


>
>
> LOOKUP - find a value in a table and return a value from a different
> column to be used in the formula (e.g. a lookup a drug code in a table and
> return minstock levels or unit prices for drugs).  This probably requires
> more support for lookup tables generally in DHIS-2. For example we use them
> in the PBF module to lookup dataelement tariffs based on date and
> dataelement id, they are also crucial for recoding long lists of morbidity
> into simple top 10 analyses (normally facilities report 4 or 5 types of
> Malaria, 4 types of diarrhea and even more ARI-related illnesses). Unless
> you group them together through a translation table they never rise up to
> the top 10 like they should. This is tedious to maintain by creating
> indicators for each group of diseases - and you can lose the dimensions
> unless you specifically create an indicator for each age/sex group of each
> disease.   A translation table does this quickly and more efficiently.
>
>
>

I am not sure if we want to go down the route of allowing queries to
different tables in indicators. We could rather think of enhancing on the
"constant" function or similar.


>  Maybe they are in a distant blueprint somewhere.
>
>
>
> On another note, I like the idea of pulling data from the analytics tables
> into iReports for improved performance, but haven't found out a simple way
> to pull data from time periods that crossover different years.  How are you
> doing that in the API - LAST_TWELVE_MONTHS, etc....? Do you just join all of
> the needed analytics tables together based on the date
>

This is implemented in the application (Java) layer and is slightly
complex, but basically does a sql union statement across both partitions
(analytics tables). My best suggestion would be to create a SQL view using
union to "concatenate" the content of all of the anlaytics tables and then
do a query against that view. Remember to create that view through dhis and
not just directly in the db. On second thought I think we might need to
drop/recreate sql views at the start/beginning of the analytics generation
process.

regards,

Lars




>  Randy
>
>
>
>
>
> *From:* Dhis2-users [mailto:dhis2-users-bounces+rwilson=
> msh.org@xxxxxxxxxxxxxxxxxxx] *On Behalf Of *Bob Jolliffe
> *Sent:* Thursday, April 03, 2014 6:53 PM
> *To:* Lars Helge Øverland
> *Cc:* dhis2-users@xxxxxxxxxxxxxxxxxxx
> *Subject:* Re: [Dhis2-users] Spreading population growth by period
>
>
>
> On 3 April 2014 18:11, Lars Helge Øverland <larshelge@xxxxxxxxx> wrote:
>
> dHi,
>
>
>
> to me it sounds like storing monthly (or quarterly) population data in the
> system would be the only short/medium-term solution. It will work fine as
> long as data element aggregation operator is set to average.
>
>
>
> This to me sounds like an ideal candidate for an app<http://www.dhis2.org/doc/snapshot/en/user/html/ch29.html>- one that takes i) the org unit level for the yearly population data and
> ii) the growth rate as input, then applies a linear data addition along the
> 12 months to make more realistic population data, writes that monthly data
> back to the system and removes the yearly data after verification.
>
>
>
> Sounds like a way to do it.  I think I'd maybe leave the yearly population
> rather than delete it and name the calculated dataelements slightly
> different.
>
>
>
> The alternative would be to do something smart with the indicator
> calculation eg. an option to say use the extrapolated (or interpolated for
> previous years) value from the yearly dataelement when it is being combined
> in calculation with a monthly.  It should be possible to linearly
> extrapolate on the fly on the basis of previous year's value.
>
>
>
> Though persisting the monthly values from the calculation is probably
> simpler and easier on the database.
>
>
>
>
>
> Or less sexy, a bash and curl script against the web api.
>
>
>
> Even less sexy bash + psql against the database :-)
>
>
>
>
> Lars
>
>
>
>
>
>
>
>
>
> On Thu, Apr 3, 2014 at 1:11 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
> We face exactly the same issue,except it is even more problematic because
> we are analyzing primarily weekly data. Our solution has been exactly as
> you have written, to create and import the facility catchment population
> externally and import. It is a bit of effort but only needs to be done
> once. A better solution would be preferable though.
>
> Regards,
> Jason
>
> --
> Sent from my mobile
>
> On Apr 3, 2014 12:43 PM, "Wilson,Randy" <rwilson@xxxxxxx> wrote:
>
>   Hi all,
>
>
>
> We have a couple of programs that look at their population coverage
> indicators each month (e.g. ANC 1 coverage rate), yet we only update
> populations once a year (entering the data or adding projected figures in
> January).
>
>
>
> The problem is when you look at coverage monthly you always see a dip in
> January (see chart below).  Is there an established way to spread the
> population by period?  I've got the aggregation operator for the Population
> data element set to "Average" - but presumably that would only have an
> impact on yearly data if we were analyzing more than 1 year.
>
>
>
> I can smooth  this outside of DHIS-2 by dividing the annual population
> growth rate by 12 and applying a monthly increase to the January population
> before calculating coverage.  What are the other options?
>
>
>
> We could enter monthly population projections into the DHIS-2 - but seems
> like a lot of extra effort and at the facility level they only re-estimate
> population once a year.
>
>
>
>
>
>
>
>
>
> *Randy Wilson*
>
>
>
>
>
> _______________________________________________
> 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
>
>
>
>
> _______________________________________________
> 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
>
>
>