Spreading population growth by period and enhancing indicator functions

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:

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

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.

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

Randy

···

On 3 April 2014 18:11, Lars Helge Øverland larshelge@gmail.com 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
- 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 :slight_smile:

Lars

On Thu, Apr 3, 2014 at 1:11 PM, Jason Pickering jason.p.pickering@gmail.com 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@msh.org 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

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


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

Hi Randy,

thanks for the input.

···

On Fri, Apr 4, 2014 at 10:28 AM, Wilson,Randy rwilson@msh.org 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@lists.launchpad.net] On Behalf Of Bob Jolliffe

Sent: Thursday, April 03, 2014 6:53 PM

To: Lars Helge Øverland

Cc: dhis2-users@lists.launchpad.net

Subject: Re: [Dhis2-users] Spreading population growth by period

On 3 April 2014 18:11, Lars Helge Øverland larshelge@gmail.com 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
- 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 :slight_smile:

Lars

On Thu, Apr 3, 2014 at 1:11 PM, Jason Pickering jason.p.pickering@gmail.com 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@msh.org 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

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


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