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 <rme...@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
> 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 | *rme...@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 <larshe...@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
>>
>>
>
>
> _______________________________________________
> 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
>
>
_______________________________________________
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
