dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #33000
Re: [Dhis2-users] DHIS2 - Averaging over orgunits
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@xxxxxxxx> 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@xxxxxxxxx> 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
>
> On 24 September 2014 09:55, Rodolfo Melia <rmelia@xxxxxxxxxxxx> 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
>> 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@xxxxxxxxxxxx
>> 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@xxxxxxxxx
>> > 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
>>>
>>>
>>
>>
>> _______________________________________________
>> 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
>
>
>
Follow ups
References
-
Reporting Issue with Yearly & Average Data Elements
From: Maheed Ramadan, 2014-08-27
-
Re: Reporting Issue with Yearly & Average Data Elements
From: Lars Helge Øverland, 2014-08-27
-
DHIS2 - Averaging over orgunits
From: Robin Martens, 2014-09-04
-
Re: DHIS2 - Averaging over orgunits
From: Lars Helge Øverland, 2014-09-05
-
Re: DHIS2 - Averaging over orgunits
From: Robin Martens, 2014-09-05
-
Re: DHIS2 - Averaging over orgunits
From: Rodolfo Melia, 2014-09-07
-
Re: DHIS2 - Averaging over orgunits
From: Lars Helge Øverland, 2014-09-07
-
Re: DHIS2 - Averaging over orgunits
From: Robin Martens, 2014-09-08
-
Re: DHIS2 - Averaging over orgunits
From: Lars Helge Øverland, 2014-09-24
-
Re: DHIS2 - Averaging over orgunits
From: Rodolfo Melia, 2014-09-24
-
Re: DHIS2 - Averaging over orgunits
From: Bob Jolliffe, 2014-09-24
-
Re: [Dhis2-users] DHIS2 - Averaging over orgunits
From: Adebusoye Anifalaje, 2014-09-24