# [Dhis2-devs] Average Sum in Org unit Hierarchy

Hi Lars,
Many thanks for your response. Adding a bi-weekly period type will definitely be a good feature to have. However in my example screen shot the value for one month e.g. July is 20 and since it was collected for one week then the average is 20/1 which is 20. What the system is doing is 20 /(31/7) = 4.5,given July has 31 days which means the system is including weeks in which no data was collected and mathematically this is incorrect. If formula for calculating this can be fixed to be a count of the weeks in which data is entered (including zeroes) then it would be mathematically correct instead of including periods in which no data is collected.

rgds

David

···

2016-11-17 20:49 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi David,

the way the average+sum aggregation works is roughly this:

sum( [days in data period] X data value ) / [days-in-aggregation-period]

In other words, we take the sum of the product of no of days in the data value period and data value, divided by no of days in aggregation period.

Data value period is the period for which the raw data is collected (weekly in your case). Aggregation period is the period which is requested in analytics (monthly and quarterly in your case).

So in this case, the problem is that you capture data by weeks which in reality represent bi-weeks - this confuses the aggregation output. The holes in the data (you have many weeks without data) will pull down the average score. Based on the rules outlined the results seems correct from what I can see.

We could rather look into adding support for a bi-weekly period type.

best regards,

Lars

On Wed, Nov 16, 2016 at 2:24 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Lars,
Many thanks for your response, since we do not have biweekly defined as a dhis2 period type(which would be great to have), we have configured the dataset to be weekly but only that data is entered on a biweekly basis, meaning one week will miss data while another will have based on when it was collected. I have attached an excel download from the datavalues table as well as a screen shot showing the weekly periods that data is collected.

rgds

David

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

2016-11-16 14:08 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi David,

sounds strange indeed. We need some more info. Can you include bi-weekly periods in your screenshot so that we can see what the original captured value is?

regards,

Lars

On Mon, Nov 14, 2016 at 8:50 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Team,
I have a data element set us Average sum in org unit hierarchy and collected on a bi weekly basis, when I select the data in the pivot table, the average values are bizarre(see attachment). Is this how the above aggregation operator is meant to work or its a bug?

rgds

David

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Hi Muturi,

yes I see you point. This is something we could look into supporting as a setting (include only periods with data in average denominator). I get’s a bit tricky to implement as we need to weigh the values according to the number of days in the data period, and since we need to calculate all this with acceptable performance. We will look into it.

Lars

···

On Thu, Nov 17, 2016 at 7:02 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Lars,
Many thanks for your response. Adding a bi-weekly period type will definitely be a good feature to have. However in my example screen shot the value for one month e.g. July is 20 and since it was collected for one week then the average is 20/1 which is 20. What the system is doing is 20 /(31/7) = 4.5,given July has 31 days which means the system is including weeks in which no data was collected and mathematically this is incorrect. If formula for calculating this can be fixed to be a count of the weeks in which data is entered (including zeroes) then it would be mathematically correct instead of including periods in which no data is collected.

rgds

David

2016-11-17 20:49 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi David,

the way the average+sum aggregation works is roughly this:

sum( [days in data period] X data value ) / [days-in-aggregation-period]

In other words, we take the sum of the product of no of days in the data value period and data value, divided by no of days in aggregation period.

Data value period is the period for which the raw data is collected (weekly in your case). Aggregation period is the period which is requested in analytics (monthly and quarterly in your case).

So in this case, the problem is that you capture data by weeks which in reality represent bi-weeks - this confuses the aggregation output. The holes in the data (you have many weeks without data) will pull down the average score. Based on the rules outlined the results seems correct from what I can see.

We could rather look into adding support for a bi-weekly period type.

best regards,

Lars

On Wed, Nov 16, 2016 at 2:24 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Lars,
Many thanks for your response, since we do not have biweekly defined as a dhis2 period type(which would be great to have), we have configured the dataset to be weekly but only that data is entered on a biweekly basis, meaning one week will miss data while another will have based on when it was collected. I have attached an excel download from the datavalues table as well as a screen shot showing the weekly periods that data is collected.

rgds

David

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

2016-11-16 14:08 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi David,

sounds strange indeed. We need some more info. Can you include bi-weekly periods in your screenshot so that we can see what the original captured value is?

regards,

Lars

On Mon, Nov 14, 2016 at 8:50 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Team,
I have a data element set us Average sum in org unit hierarchy and collected on a bi weekly basis, when I select the data in the pivot table, the average values are bizarre(see attachment). Is this how the above aggregation operator is meant to work or its a bug?

rgds

David

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Hi Lars,
Many thanks, we will eagerly wait for a solution. I have noticed there is a blue print related to this i.e. although it does not directly fix it but might be an alternative solution as we wait for this feature.

rgds

David

···

2016-11-18 14:29 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi Muturi,

yes I see you point. This is something we could look into supporting as a setting (include only periods with data in average denominator). I get’s a bit tricky to implement as we need to weigh the values according to the number of days in the data period, and since we need to calculate all this with acceptable performance. We will look into it.

Lars

On Thu, Nov 17, 2016 at 7:02 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Lars,
Many thanks for your response. Adding a bi-weekly period type will definitely be a good feature to have. However in my example screen shot the value for one month e.g. July is 20 and since it was collected for one week then the average is 20/1 which is 20. What the system is doing is 20 /(31/7) = 4.5,given July has 31 days which means the system is including weeks in which no data was collected and mathematically this is incorrect. If formula for calculating this can be fixed to be a count of the weeks in which data is entered (including zeroes) then it would be mathematically correct instead of including periods in which no data is collected.

rgds

David

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

2016-11-17 20:49 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi David,

the way the average+sum aggregation works is roughly this:

sum( [days in data period] X data value ) / [days-in-aggregation-period]

In other words, we take the sum of the product of no of days in the data value period and data value, divided by no of days in aggregation period.

Data value period is the period for which the raw data is collected (weekly in your case). Aggregation period is the period which is requested in analytics (monthly and quarterly in your case).

So in this case, the problem is that you capture data by weeks which in reality represent bi-weeks - this confuses the aggregation output. The holes in the data (you have many weeks without data) will pull down the average score. Based on the rules outlined the results seems correct from what I can see.

We could rather look into adding support for a bi-weekly period type.

best regards,

Lars

On Wed, Nov 16, 2016 at 2:24 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Lars,
Many thanks for your response, since we do not have biweekly defined as a dhis2 period type(which would be great to have), we have configured the dataset to be weekly but only that data is entered on a biweekly basis, meaning one week will miss data while another will have based on when it was collected. I have attached an excel download from the datavalues table as well as a screen shot showing the weekly periods that data is collected.

rgds

David

Lars Helge Øverland

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

2016-11-16 14:08 GMT+03:00 Lars Helge Øverland lars@dhis2.org:

Hi David,

sounds strange indeed. We need some more info. Can you include bi-weekly periods in your screenshot so that we can see what the original captured value is?

regards,

Lars

On Mon, Nov 14, 2016 at 8:50 PM, David Muturi dnmuturi@gmail.com wrote:

Hi Team,
I have a data element set us Average sum in org unit hierarchy and collected on a bi weekly basis, when I select the data in the pivot table, the average values are bizarre(see attachment). Is this how the above aggregation operator is meant to work or its a bug?

rgds

David