dhis2-users team mailing list archive

[Orvalho Augusto <orvaquim@xxxxxxxxx>]

OK.

This approach is fine and I like it. But the caveats are the easy messing
around the datavalue table and the almost deprecated datamart.

Caveman





On Thu, Dec 12, 2013 at 5:08 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi Caveman,
>
> The way we deal with missing data is to
>
> 0) Get your data values. We did it per data
> element/sourceid/categoryoptioncomboid for the data elements which should
> be cumulatively summed. Not particularly efficient really, but saved on
> memory.
> 1) Determine the start and end dates of the data series in question. As an
> example, the data value series might start in Jan 2011 and go until
> December 2013. These would define the start and end-dates for the
> calculation with a determined periodicity.
> 2) Synthesize a time series based on the required periodicity, taking into
> account the start and end dates. For instance, all months between Jan 2011
> and Dec 2013.
> 3) Merge this aggregated data with the full list of periods , ensuring you
> will get NAs for missing periods
> 4) Change all NAs to zeros. There might be better ways to handle this,
> with the "zoo"package.
> 5) Finally, calculate the running total using which every method might be
> appropriate. There are several different ways to do this.
>
> After this, you can write the data wherever it might need to go. We wrote
> it back to the data value table, so that it was available through the
> datamart.
>
> It would be good I think to have a "Cumulative Sum" operator which would
> work similar to this over the time dimension in DHIS2, rather than having
> to worry about these external methods.
>
> Regards,
> Jason
>
>
>
> On Thu, Dec 12, 2013 at 3:42 PM, Orvalho Augusto <orvaquim@xxxxxxxxx>wrote:
>
>> Good idea Jason!
>>
>> I had a long time ago this issue. Number of cumulative ARV patiens or
>> Number of cumulative patients on TB something. I wrote a R script which got
>> data from the datavalue table rearanged it and produced a new table with
>> the cumulatives. A table for each type of period. Then the report used
>> these tables to report it.
>>
>> The problems:
>> 1. Always rember to produce these tables manually out of DHIS when your
>> data is updated
>> 2. How to deal with missing data from one previous period. Eg: I have
>> January, February and April. There is no March (I used to make it zero).
>>
>> Eventually I left the project. And I do not know how they do now. But any
>> way cummulative operators are welcomed.
>>
>> Caveman
>>
>>
>>
>>
>> On Thu, Dec 12, 2013 at 10:07 AM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> Maybe I am am overcomplicating it, but I suppose it depends on what you
>>> mean by cumulative and how you need the figures to be displayed.
>>>
>>> Another way to do it with Excel PivotTables is by
>>>
>>> 1) Download some data from the DHIS2 Pivot table module
>>> 2) Be sure you cast "Periods" to a real date format with (=DATEVALUE()),
>>> otherwise, the order will not be right.
>>> 3) Setup your Pivot and change the "Value Field Settings" to "Sum" and
>>> "Show values as" to "Running total in" "date".
>>>
>>> I have attached a simple Excel sheet which shows how to accomplish this.
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>>
>>> On Thu, Dec 12, 2013 at 9:48 AM, Lars Helge Øverland <
>>> larshelge@xxxxxxxxx> wrote:
>>>
>>>> Hi there,
>>>>
>>>> one trick here could be to create a pivot table with the required data
>>>> elements where you simply move the period dimension out of the table
>>>> completely. This should put no constraints on time dimension and give you
>>>> all values.
>>>>
>>>> Then I guess the next requirement will be running total "since" a date
>>>> and "as of" a date, here we will have to invemt something.
>>>>
>>>> Lars
>>>> On Dec 12, 2013 6:04 AM, "Knut Staring" <knutst@xxxxxxxxx> wrote:
>>>>
>>>>> I certainly agree that your method is much more sophisticated and
>>>>> flexible, and would love to have something like this available through the
>>>>> API - but going back to Eddie's initial post, it seems to me his specific
>>>>> goal could be achieved relatively simply. It would be different if he goes
>>>>> beyond the current year, or if he needs to display the cumulative figures
>>>>> for all the months separately.
>>>>>
>>>>>
>>>>> On Thu, Dec 12, 2013 at 5:36 AM, Jason Pickering <
>>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>>
>>>>>> Hi Knut,
>>>>>> This is not quite the same thing. There may be data for time periods
>>>>>> earlier than 2013 of course.
>>>>>>
>>>>>> But as I have implemented this requirement other places in the form
>>>>>> of reports/scripts to generate this data, if the following data is reported
>>>>>>
>>>>>> Dec 2012 9
>>>>>> Jan 2013 10
>>>>>> Feb 2013 15
>>>>>> March 2013 14
>>>>>> ..
>>>>>>
>>>>>> The cumulative "indicator" for this would be
>>>>>> Dec 2012 9
>>>>>> Jan 2013 19
>>>>>> Feb 2013 34
>>>>>> March 2013 48
>>>>>> ...
>>>>>>
>>>>>> These sorts of indicators are  needed when calculating things like
>>>>>> "Number of people who have ever started ART", where "New ART starts" are
>>>>>> reported each month. So instead of having the facility to calculate "Number
>>>>>> of people who have every started ART", it can be calculated with a
>>>>>> cumulative/running total instead from the new figures reported each month.
>>>>>> I think this arises from the lack of a "RUNNING SUM" operator for the time
>>>>>> dimension in DHIS2, which probably should be there.
>>>>>>
>>>>>> Best regards,
>>>>>> Jason
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Thu, Dec 12, 2013 at 2:53 AM, Knut Staring <knutst@xxxxxxxxx>wrote:
>>>>>>
>>>>>>> I think you could just take the yearly data for 2013. This should
>>>>>>> add up what is available so far this year.
>>>>>>>
>>>>>>> Sent from my mobile
>>>>>>> On 12 Dec 2013 01:55, "Jason Pickering" <jason.p.pickering@xxxxxxxxx>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Eddie,
>>>>>>>>
>>>>>>>> You certainly could, but there is no way to do this directly with
>>>>>>>> DHIS2 I think . You could use windowing functions of Postgresql [0] to
>>>>>>>> calculate the cumulative sum, or create your own function with a custom
>>>>>>>> function with PL/R which has been described in the documentation outlined
>>>>>>>> here [1]. There are various ways to do this in R, either with the base
>>>>>>>> "cumsum" function or with more advanced methods in the "timeSeries" package
>>>>>>>> depending on your exact needs.
>>>>>>>>
>>>>>>>> The simplest method however might be to use Excel [2] .
>>>>>>>>
>>>>>>>> However at the moment, there is no way to do this directly with
>>>>>>>> DHIS2 as far as I know. But if others know more direct methods, it would be
>>>>>>>> good to know!
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Jason
>>>>>>>>
>>>>>>>>
>>>>>>>> [0]
>>>>>>>> http://www.postgresonline.com/journal/archives/47-How-to-calculate-Running-Totals-and-Sums-in-SQL.html
>>>>>>>> [1] http://www.dhis2.org/doc/snapshot/en/user/html/apcs06.html
>>>>>>>> [2]http://support.microsoft.com/kb/214149
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Dec 11, 2013 at 1:50 PM, Edwin Mulwa <eddiemu@xxxxxxxxx>wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> I would like to create a report that has a field(s) which is a
>>>>>>>>> cumulative total calculation involving multiple data elements. For example,
>>>>>>>>> I have data elements A and B and I am doing monthly cumulative reporting
>>>>>>>>> such that the value for element A in Feb will be the value of A in Jan +
>>>>>>>>> the new value for B in Feb, the value for A in March will be the value of A
>>>>>>>>> in Feb + the new value for B in March, etc.
>>>>>>>>>
>>>>>>>>> Can we create such a report in DHIS?
>>>>>>>>>
>>>>>>>>> Edwin
>>>>>>>>>
>>>>>>>>> _______________________________________________
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Knut Staring
>>>>> Dept. of Informatics, University of Oslo
>>>>> +4791880522
>>>>> http://dhis2.org
>>>>>
>>>>> _______________________________________________
>>>>> 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
>>>>>
>>>>>
>>>
>>> _______________________________________________
>>> 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
>>>
>>>
>>
>