Reporting Issue with Yearly & Average Data Elements

Dear All,

I was testing the demo database and found the following issue.

As you see in the 1st screenshot, I added the “Total Population” data element to the pivot table, with the last 12 months on the left side.

Here all values of the data element are the same and this is logical since the “Total Population” data element is configured to use “Yearly & Average” aggregation method

However, if we remove the “period” element to the Filter section, the pivot will show the “Total population” in one cell with the value = 11,089,164 which is the Sum of the data element value for both year 2013 and 2014. (see 2nd
picture below).

Is this a correct calculation? I think the result should stay the same since it should be averaging the values in both years and the value should stay = 5,544,582.

Awaiting your feedback if this is a bug or not.

Thank you.

Maheed.

Hi Maheed,

though maybe not intuitive, this is the way it works at the moment. Let me try to explain: The aggregation operator applies for a single aggregation period only. So if you ask for the aggregated value for a year, and the data is collected quarterly, it will return the average of those quarters. Currently, if you have multiple periods as filter, it will sum across those periods.

We could have refined it and said that if the data element has the average operator, we also average across filter time periods. However it would be a bit complex - if there are multiple data elements in the pivot, all with different aggregation operators (sum, average, count), we would not know which operator to use.

That said, I do see the need for averaging across multiple years and we could have made it so that it uses the aggregation operator from the data elements given that all data elements share the same operator, and if not, default to sum.

regards,

Lars

image

image

···

On Wed, Aug 27, 2014 at 2:59 PM, Maheed Ramadan maheed.ramadan@nrc.no wrote:

Dear All,

I was testing the demo database and found the following issue.

As you see in the 1st screenshot, I added the “Total Population” data element to the pivot table, with the last 12 months on the left side.

Here all values of the data element are the same and this is logical since the “Total Population” data element is configured to use “Yearly & Average” aggregation method

However, if we remove the “period” element to the Filter section, the pivot will show the “Total population” in one cell with the value = 11,089,164 which is the Sum of the data element value for both year 2013 and 2014. (see 2nd
picture below).

Is this a correct calculation? I think the result should stay the same since it should be averaging the values in both years and the value should stay = 5,544,582.

Awaiting your feedback if this is a bug or not.

Thank you.

Maheed.

Hi there,

As per the manual, the "average" option when defining data elements aggregates by average over periods but sum over orgunits. This limits severely the reporting possibilities, for instance when calculating the average person age over multiple districts but I can think of many other examples (prices, age, education grades,...). Is there any way to configure DHIS2 to average over orgunits?

Thank you,

Robin

Hi Robin,

I get your point. This has actually been raised before and we plan to support individual aggregation operators for the org unit and time dimensions. Blueprint here:

https://blueprints.launchpad.net/dhis2/+spec/orgunit-hierarchy-aggregation-operator

regards,

Lars

···

On Thu, Sep 4, 2014 at 12:49 PM, Robin Martens martens@sher.be wrote:

Hi there,

As per the manual, the “average” option when defining data elements aggregates by average over periods but sum over orgunits. This limits severely the reporting possibilities, for instance when calculating the average person age over multiple districts but I can think of many other examples (prices, age, education grades,…). Is there any way to configure DHIS2 to average over orgunits?

Thank you,

Robin

Hi Lars,

Thank you. I thought so but couldn’t find the reference.

In the blueprint it is assigned to release 2.17, is this still being maintained?

Regards,

Robin

···

From: Lars Helge Øverland [mailto:larshelge@gmail.com]

Sent: 05 September 2014 18:24

To: Robin Martens

Cc: dhis2-users; dhis2-devs@lists.launchpad.net

Subject: Re: DHIS2 - Averaging over orgunits

Hi Robin,

I get your point. This has actually been raised before and we plan to support individual aggregation operators for the org unit and time dimensions. Blueprint here:

https://blueprints.launchpad.net/dhis2/+spec/orgunit-hierarchy-aggregation-operator

regards,

Lars

On Thu, Sep 4, 2014 at 12:49 PM, Robin Martens martens@sher.be wrote:

Hi there,

As per the manual, the “average” option when defining data elements aggregates by average over periods but sum over orgunits. This limits severely the reporting possibilities, for instance when calculating the average person age over multiple districts but I can think of many other examples (prices, age, education grades,…). Is there any way to configure DHIS2 to average over orgunits?

Thank you,

Robin

Hi Lars,

Thank you. I thought so but couldn’t find the reference.

In the blueprint it is assigned to release 2.17, is this still being maintained?

Regards,

Robin

···

From: Lars Helge Øverland [mailto:larshelge@gmail.com]

Sent: 05 September 2014 18:24

To: Robin Martens

Cc: dhis2-users; dhis2-devs@lists.launchpad.net

Subject: Re: DHIS2 - Averaging over orgunits

Hi Robin,

I get your point. This has actually been raised before and we plan to support individual aggregation operators for the org unit and time dimensions. Blueprint here:

https://blueprints.launchpad.net/dhis2/+spec/orgunit-hierarchy-aggregation-operator

regards,

Lars

On Thu, Sep 4, 2014 at 12:49 PM, Robin Martens martens@sher.be wrote:

Hi there,

As per the manual, the “average” option when defining data elements aggregates by average over periods but sum over orgunits. This limits severely the reporting possibilities, for instance when calculating the average person age over multiple districts but I can think of many other examples (prices, age, education grades,…). Is there any way to configure DHIS2 to average over orgunits?

Thank you,

Robin

Hi there,

we will try to get it in for 2.17.

One question around the design: We currently have 7 aggregation operators:

sum | average | count | std dev | variance | min | max

Re individual aggregation operators for the time and org unit dimension, from a technical perspective we are not first aggregating in time, then in org unit dimension, rather aggregating it all in a single query to achieve good performance.

Allowing for 7 x 7 = 49 possible combinations of aggregation operators will be complex to implement and some combinations might not be meaningful. I am sensing that it is doing average across both time and org unit which is the most useful combination we currently lack. Implementing this will be straight-forward and could even be back-ported to 2.16.

So the question is: Will it be sufficient to come up with the most useful pre-defined combinations of time and org unit aggregation operators, or do we need to have full flexibility and allow all aggregation operators in both dimensions?

If the former then please suggest which combinations are most useful.

Already on the list:

Sum

Average

Org unit: Average - Time: Sum

Org unit: Sum - Time: Average
Count

Std dev

Variance

Min

Max

regards,

Lars

···

On Sun, Sep 7, 2014 at 2:10 AM, Rodolfo Melia rmelia@knowming.com wrote:

Hi - i just want to add support to the importance of splitting the aggregator operator across time and org units. I’m planning around its availability for 2.17

Rodolfo Meliá

On 5 Sep 2014, at 17:26, Robin Martens martens@sher.be wrote:

Hi Lars,

Thank you. I thought so but couldn’t find the reference.

In the blueprint it is assigned to release 2.17, is this still being maintained?

Regards,

Robin

From: Lars Helge Øverland [mailto:larshelge@gmail.com]

Sent: 05 September 2014 18:24

To: Robin Martens

Cc: dhis2-users; dhis2-devs@lists.launchpad.net

Subject: Re: DHIS2 - Averaging over orgunits

Hi Robin,

I get your point. This has actually been raised before and we plan to support individual aggregation operators for the org unit and time dimensions. Blueprint here:

https://blueprints.launchpad.net/dhis2/+spec/orgunit-hierarchy-aggregation-operator

regards,

Lars

On Thu, Sep 4, 2014 at 12:49 PM, Robin Martens martens@sher.be wrote:

Hi there,

As per the manual, the “average” option when defining data elements aggregates by average over periods but sum over orgunits. This limits severely the reporting possibilities, for instance when calculating the average person age over multiple districts but I can think of many other examples (prices, age, education grades,…). Is there any way to configure DHIS2 to average over orgunits?

Thank you,

Robin


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

Hi Lars,

In my case it is definitely the averaging over both periods and orgunits which is most critical, the other ones are more “nice to have”.

A backporting to 2.16 would be highly appreciated if possible.

Kind regards,

Robin

···

From: Lars Helge Øverland [mailto:larshelge@gmail.com]

Sent: 07 September 2014 16:24

To: Rodolfo Melia

Cc: Robin Martens; dhis2-users; dhis2-devs@lists.launchpad.net

Subject: Re: [Dhis2-devs] DHIS2 - Averaging over orgunits

Hi there,

we will try to get it in for 2.17.

One question around the design: We currently have 7 aggregation operators:

sum | average | count | std dev | variance | min | max

Re individual aggregation operators for the time and org unit dimension, from a technical perspective we are not first aggregating in time, then in org unit dimension, rather aggregating it all in a single query to achieve good performance.

Allowing for 7 x 7 = 49 possible combinations of aggregation operators will be complex to implement and some combinations might not be meaningful. I am sensing that it is doing average across both time and org unit which is the most useful combination we currently lack. Implementing this will be straight-forward and could even be back-ported to 2.16.

So the question is: Will it be sufficient to come up with the most useful pre-defined combinations of time and org unit aggregation operators, or do we need to have full flexibility and allow all aggregation operators in both dimensions?

If the former then please suggest which combinations are most useful.

Already on the list:

Sum

Average

Org unit: Average - Time: Sum

Org unit: Sum - Time: Average

Count

Std dev

Variance

Min

Max

regards,

Lars

On Sun, Sep 7, 2014 at 2:10 AM, Rodolfo Melia rmelia@knowming.com wrote:

Hi - i just want to add support to the importance of splitting the aggregator operator across time and org units. I’m planning around its availability for 2.17

Rodolfo Meliá

On 5 Sep 2014, at 17:26, Robin Martens martens@sher.be wrote:

Hi Lars,

Thank you. I thought so but couldn’t find the reference.

In the blueprint it is assigned to release 2.17, is this still being maintained?

Regards,

Robin

From: Lars Helge Øverland [mailto:larshelge@gmail.com]

Sent: 05 September 2014 18:24

To: Robin Martens

Cc: dhis2-users;
dhis2-devs@lists.launchpad.net

Subject: Re: DHIS2 - Averaging over orgunits

Hi Robin,

I get your point. This has actually been raised before and we plan to support individual aggregation operators for the org unit and time dimensions. Blueprint here:

https://blueprints.launchpad.net/dhis2/+spec/orgunit-hierarchy-aggregation-operator

regards,

Lars

On Thu, Sep 4, 2014 at 12:49 PM, Robin Martens martens@sher.be wrote:

Hi there,

As per the manual, the “average” option when defining data elements aggregates by average over periods but sum over orgunits. This limits severely the reporting possibilities, for instance when calculating the average person age over multiple districts but I can think of many other examples (prices, age, education grades,…). Is there any way to configure DHIS2 to average over orgunits?

Thank you,

Robin


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

Hi there,

we have implemented support for a "true" average aggregation operator in
trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to "Average (sum in
organisation unit hierarchy)" in the UI.

This will be part of 2.17.

regards,

Lars

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg

  2. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null

  3. LAST (across time only)

  4. Std Deviation

  5. everything else

···

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

For what its worth the following is the sql I used to extract the latest value of a population dataelement. I am sure it could be generalized and maybe implemented as a getLastValue method on a dataelement.

···

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

Hi Bob,

I do not suppose you could use this statement in the sqlview since that’s limited to Select statements. I recall Jason mentioned that the only way they are able to manage a WASH Program needing just the last current value is to dump the data and post updates though the web api. This seems to be way too much maintenance and would be better if one could use an sql statement like yours instead.

Thanks

Regards,

Busoye

···

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

Or have it as part of the aggregator operators… That’s the dream

Rodolfo Meliá

+44 777 576 4090 | +1 708 872-7636

Please ignore any typos on this email sent from my mobile, probably written while I was at the park with the kids, at the airport walking to the gate, or sleepless (although exhausted). Grammatical faults? I’m sorry- I do my best

···

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

You could install the function into the database through the postgres backend.

Then you can include the function into select statements. For example:

select organisationunit.name, getLatestPopulation( organisationunit.organisationunitid).value from organisationunit;

···

On 24 September 2014 10:22, Adebusoye Anifalaje busoye@hisp.org wrote:

Hi Bob,

I do not suppose you could use this statement in the sqlview since that’s limited to Select statements. I recall Jason mentioned that the only way they are able to manage a WASH Program needing just the last current value is to dump the data and post updates though the web api. This seems to be way too much maintenance and would be better if one could use an sql statement like yours instead.

Thanks

Regards,

Busoye
On 24 Sep 2014, at 10:16, Bob Jolliffe bobjolliffe@gmail.com wrote:

For what its worth the following is the sql I used to extract the latest value of a population dataelement. I am sure it could be generalized and maybe implemented as a getLastValue method on a dataelement.


– function returns most recent population estimate (and year) for

– an orgunitid


CREATE OR REPLACE FUNCTION getLatestPopulation(orgunitid int, out value, out year)

AS $$

BEGIN

SELECT DISTINCT ON (sourceid)

value as population,

extract(year from startdate)

INTO

value, year

FROM datavalue

join period on period.periodid=datavalue.periodid

join organisationunit on organisationunit.organisationunitid=datavalue.sourceid

join dataelement on dataelement.dataelementid=datavalue.dataelementid

WHERE

organisationunit.organisationunitid = orgunitid AND

– better to use domething like dataelement.code=‘POP’

– better still to parameterize it

dataelement.name =‘Population Total’ AND

extract(year from startdate) <= extract(‘year’ from current_timestamp)

ORDER BY sourceid,startdate DESC;

END


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

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

Thank you Bob. I guess if Rodolfo’s dream doesn’t come true, then this would be the next best solution.

Cheers.

Busoye

···

On 24 September 2014 10:22, Adebusoye Anifalaje busoye@hisp.org wrote:

Hi Bob,

I do not suppose you could use this statement in the sqlview since that’s limited to Select statements. I recall Jason mentioned that the only way they are able to manage a WASH Program needing just the last current value is to dump the data and post updates though the web api. This seems to be way too much maintenance and would be better if one could use an sql statement like yours instead.

Thanks

Regards,

Busoye
On 24 Sep 2014, at 10:16, Bob Jolliffe bobjolliffe@gmail.com wrote:

For what its worth the following is the sql I used to extract the latest value of a population dataelement. I am sure it could be generalized and maybe implemented as a getLastValue method on a dataelement.


– function returns most recent population estimate (and year) for

– an orgunitid


CREATE OR REPLACE FUNCTION getLatestPopulation(orgunitid int, out value, out year)

AS $$

BEGIN

SELECT DISTINCT ON (sourceid)

value as population,

extract(year from startdate)

INTO

value, year

FROM datavalue

join period on period.periodid=datavalue.periodid

join organisationunit on organisationunit.organisationunitid=datavalue.sourceid

join dataelement on dataelement.dataelementid=datavalue.dataelementid

WHERE

organisationunit.organisationunitid = orgunitid AND

– better to use domething like dataelement.code=‘POP’

– better still to parameterize it

dataelement.name =‘Population Total’ AND

extract(year from startdate) <= extract(‘year’ from current_timestamp)

ORDER BY sourceid,startdate DESC;

END


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

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

The problem with that approach is that the function is not available on Pivot Tables or Event Visualizer, where people need it… Anyway, nice to know that you can call it via the SQL views.

···

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 11:03 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

You could install the function into the database through the postgres backend.

Then you can include the function into select statements. For example:

select organisationunit.name, getLatestPopulation( organisationunit.organisationunitid).value from organisationunit;

On 24 September 2014 10:22, Adebusoye Anifalaje busoye@hisp.org wrote:

Hi Bob,

I do not suppose you could use this statement in the sqlview since that’s limited to Select statements. I recall Jason mentioned that the only way they are able to manage a WASH Program needing just the last current value is to dump the data and post updates though the web api. This seems to be way too much maintenance and would be better if one could use an sql statement like yours instead.

Thanks

Regards,

Busoye
On 24 Sep 2014, at 10:16, Bob Jolliffe bobjolliffe@gmail.com wrote:

For what its worth the following is the sql I used to extract the latest value of a population dataelement. I am sure it could be generalized and maybe implemented as a getLastValue method on a dataelement.


– function returns most recent population estimate (and year) for

– an orgunitid


CREATE OR REPLACE FUNCTION getLatestPopulation(orgunitid int, out value, out year)

AS $$

BEGIN

SELECT DISTINCT ON (sourceid)

value as population,

extract(year from startdate)

INTO

value, year

FROM datavalue

join period on period.periodid=datavalue.periodid

join organisationunit on organisationunit.organisationunitid=datavalue.sourceid

join dataelement on dataelement.dataelementid=datavalue.dataelementid

WHERE

organisationunit.organisationunitid = orgunitid AND

– better to use domething like dataelement.code=‘POP’

– better still to parameterize it

dataelement.name =‘Population Total’ AND

extract(year from startdate) <= extract(‘year’ from current_timestamp)

ORDER BY sourceid,startdate DESC;

END


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

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars

I do agree its not the final solution. Just thought I’d share as a useful hack/workaround for some problems. I am no sql wizard, so was quite pleased with myself that this worked :slight_smile:

···

On 24 September 2014 11:26, Rodolfo Melia rmelia@knowming.com wrote:

The problem with that approach is that the function is not available on Pivot Tables or Event Visualizer, where people need it… Anyway, nice to know that you can call it via the SQL views.

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 11:03 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

You could install the function into the database through the postgres backend.

Then you can include the function into select statements. For example:

select organisationunit.name, getLatestPopulation( organisationunit.organisationunitid).value from organisationunit;

On 24 September 2014 10:22, Adebusoye Anifalaje busoye@hisp.org wrote:

Hi Bob,

I do not suppose you could use this statement in the sqlview since that’s limited to Select statements. I recall Jason mentioned that the only way they are able to manage a WASH Program needing just the last current value is to dump the data and post updates though the web api. This seems to be way too much maintenance and would be better if one could use an sql statement like yours instead.

Thanks

Regards,

Busoye
On 24 Sep 2014, at 10:16, Bob Jolliffe bobjolliffe@gmail.com wrote:

For what its worth the following is the sql I used to extract the latest value of a population dataelement. I am sure it could be generalized and maybe implemented as a getLastValue method on a dataelement.


– function returns most recent population estimate (and year) for

– an orgunitid


CREATE OR REPLACE FUNCTION getLatestPopulation(orgunitid int, out value, out year)

AS $$

BEGIN

SELECT DISTINCT ON (sourceid)

value as population,

extract(year from startdate)

INTO

value, year

FROM datavalue

join period on period.periodid=datavalue.periodid

join organisationunit on organisationunit.organisationunitid=datavalue.sourceid

join dataelement on dataelement.dataelementid=datavalue.dataelementid

WHERE

organisationunit.organisationunitid = orgunitid AND

– better to use domething like dataelement.code=‘POP’

– better still to parameterize it

dataelement.name =‘Population Total’ AND

extract(year from startdate) <= extract(‘year’ from current_timestamp)

ORDER BY sourceid,startdate DESC;

END


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

On 24 September 2014 09:55, Rodolfo Melia rmelia@knowming.com wrote:

Hi Lars - I had a quick look in trunk. When editing a Data Element, I still only see one Aggregation Operator. I was expecting to find two: one for controlling the operator of aggregations across time, a second selector for the aggregator across Org Units…

In terms of priorities, below my list:

  1. Sum + Avg
  1. Count (You could have two type of counts: Different to 0 or Null, 2. Different to Null
  1. LAST (across time only)
  1. Std Deviation
  1. everything else

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

Rodolfo Meliá

*Principal | *rmelia@knowming.com

Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636

www.knowming.com

On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi there,

we have implemented support for a “true” average aggregation operator in trunk now, which will average across both time and org unit dimensions.

The existing average operator has been renamed to “Average (sum in organisation unit hierarchy)” in the UI.

This will be part of 2.17.

regards,

Lars