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.
···
On Thu, Dec 12, 2013 at 5:08 PM, Jason Pickering jason.p.pickering@gmail.com wrote:
Hi Caveman,
The way we deal with missing data is to
- 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.
- 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.
- 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.
- Merge this aggregated data with the full list of periods , ensuring you will get NAs for missing periods
- Change all NAs to zeros. There might be better ways to handle this, with the "zoo"package.
- 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@gmail.com 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:
- Always rember to produce these tables manually out of DHIS when your data is updated
- 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@gmail.com 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
- Download some data from the DHIS2 Pivot table module
- Be sure you cast “Periods” to a real date format with (=DATEVALUE()), otherwise, the order will not be right.
- 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
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
On Thu, Dec 12, 2013 at 9:48 AM, Lars Helge Øverland larshelge@gmail.com 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@gmail.com 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.
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
On Thu, Dec 12, 2013 at 5:36 AM, Jason Pickering jason.p.pickering@gmail.com 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
–
Knut Staring
Dept. of Informatics, University of Oslo
+4791880522
http://dhis2.org
On Thu, Dec 12, 2013 at 2:53 AM, Knut Staring knutst@gmail.com 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@gmail.com 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
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
On Wed, Dec 11, 2013 at 1:50 PM, Edwin Mulwa eddiemu@gmail.com 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp