dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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