dhis2-users team mailing list archive

[Rodolfo Melia <rmelia@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx
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@xxxxxxxxx>
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@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
>>
>>
>>
>