Cumulative Reporting?

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

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

I think you could just take the yearly data for 2013. This should add up what is available so far this year.

···

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

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

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

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

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

running_totals_pivots.xlsx (12.5 KB)

···

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

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

  1. Download some data from the DHIS2 Pivot table module
  1. Be sure you cast “Periods” to a real date format with (=DATEVALUE()), otherwise, the order will not be right.
  1. 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

Jason,

You described perfectly what I am looking for. The “Running Sum” operator is what would be useful for solving my problem.

Edwin

···

On Thu, Dec 12, 2013 at 7: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

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

Hi Caveman,

The way we deal with missing data is to

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

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

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

  4. Merge this aggregated data with the full list of periods , ensuring you will get NAs for missing periods

  5. Change all NAs to zeros. There might be better ways to handle this, with the "zoo"package.

  6. 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:

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

  1. Download some data from the DHIS2 Pivot table module
  1. Be sure you cast “Periods” to a real date format with (=DATEVALUE()), otherwise, the order will not be right.
  1. 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

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@gmail.com wrote:

Hi Caveman,

The way we deal with missing data is to

  1. 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.
  1. 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.
  1. Merge this aggregated data with the full list of periods , ensuring you will get NAs for missing periods
  1. Change all NAs to zeros. There might be better ways to handle this, with the "zoo"package.
  1. 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:

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

  1. Download some data from the DHIS2 Pivot table module
  1. Be sure you cast “Periods” to a real date format with (=DATEVALUE()), otherwise, the order will not be right.
  1. 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

Hi Jason,

Happy New Year! Wishing you the best in 2014.

I’m following up on your email below. I’m thinking of going the PL/R route because I would like to display my cumulative-based report in DHIS rather than generate it outside of DHIS. I presume that using PL/R, I can write custom functions within the DHIS database and use them to update a custom report table that I can use for generating my report from within DHIS. Does this make sense?

Is there any data dictionary documentation available publicly that describes all the tables in the DHIS database that I can reference while writing my custom functions?

Thanks!

Edwin

···

On Wed, Dec 11, 2013 at 9:54 PM, 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

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