dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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