dhis2-users team mailing list archive

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

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

Attachment: running_totals_pivots.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet