# Aggregation query builder details **URL:** \ **Category:** Support - Assistance technique\ **Created:** [24 November 2015 11:15 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067 "2015-11-24T11:15:29Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![nami](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/n/f08c70/32.png) [@nami](https://community.dhis2.org/u/nami)\ **Post date:** [24 November 2015 11:15 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/1 "2015-11-24T11:15:29Z")
Hello, I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max _\*_ _and try to select I can’t find anything and this field is_ _mandatory and the data aggregator data element and tracker data element both has sum aggregator_ _Any idea about this_ _Thanks in advance_ ---
**Author:** ![Knut\_Staring](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/knut_staring/32/32549_2.png) [@Knut\_Staring](https://community.dhis2.org/u/Knut_Staring)\ **Post date:** [25 November 2015 09:34 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/2 "2015-11-25T09:34:41Z")
Hi Nami, Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. Knut > **···** > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > Hello, > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max _\*_ _and try to select I can’t find anything and this field is_ > > > - > > - > > > _mandatory and the data aggregator data element and tracker data element both has sum aggregator_ > > > - > > - > > > _Any idea about this_ > > > - > > - > > > _Thanks in advance_ > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org) ---
**Author:** ![nami](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/n/f08c70/32.png) [@nami](https://community.dhis2.org/u/nami)\ **Post date:** [25 November 2015 19:52 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/3 "2015-11-25T19:52:03Z")
**Hello,** **I am using 2.21** **And yes I noticed that indicator will do the job since I can use program indicator in data visualizer** **Thanks** > **···** > > **From:** Knut Staring [[mailto:knutst@gmail.com](mailto:knutst@gmail.com)] > **Sent:** Wednesday, November 25, 2015 11:35 AM > **To:** Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) > **Cc:** DHIS 2 Users list [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > Hi Nami, > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > Knut > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > Hello, > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max _\*_ _and try to select I can’t find anything and this field is_ > > > - > > - > > > _mandatory and the data aggregator data element and tracker data element both has sum aggregator_ > > > - > > - > > > _Any idea about this_ > > > - > > - > > > _Thanks in advance_ > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org) ---
**Author:** ![Dipali\_Sahu](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/dipali_sahu/32/29476_2.png) [@Dipali\_Sahu](https://community.dhis2.org/u/Dipali_Sahu)\ **Post date:** [26 November 2015 10:48 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/4 "2015-11-26T10:48:31Z")
Hi Knut We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. Thank you Deepali International Medical Corps > **···** > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > Hello, > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max _\*_ _and try to select I can’t find anything and this field is_ > > > - > > - > > > _mandatory and the data aggregator data element and tracker data element both has sum aggregator_ > > > - > > - > > > _Any idea about this_ > > > - > > - > > > _Thanks in advance_ > > > > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org) > > – ---
**Author:** ![Elmarie\_Claasen](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/elmarie_claasen/32/48105_2.png) [@Elmarie\_Claasen](https://community.dhis2.org/u/Elmarie_Claasen)\ **Post date:** [26 November 2015 21:41 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/5 "2015-11-26T21:41:41Z")
Hi Knut, Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. Patient satisfaction survey rate = No of surveys conducted/No of facilities Patient satisfaction rate = No of satisfied responses/All responses It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. Regards, **Elmarie Claasen** ![Hisp logo](https://dhis2.b-cdn.net/uploads/default/original/1X/7c66cee43ee6d15fecc7390e4389276b0a8abd75.png) Project Manager Health Information Systems Program Tel: 041-367 1027 Cell: 082 374 2209 E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) Skype: elmarie.claasen52 This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > **···** > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] **On Behalf Of** Dipali Sahu > **Sent:** 26 November 2015 12:49 PM > **To:** Knut Staring > **Cc:** DHIS 2 Users list > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > Hi Knut > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > Thank you > > Deepali > > International Medical Corps > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > Hi Nami, > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > Knut > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > Hello, > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max _\*_ _and try to select I can’t find anything and this field is_ > > > - > > - > > > _mandatory and the data aggregator data element and tracker data element both has sum aggregator_ > > > - > > - > > > _Any idea about this_ > > > - > > - > > > _Thanks in advance_ > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org) > > * * * > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ ---
**Author:** ![Knut\_Staring](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/knut_staring/32/32549_2.png) [@Knut\_Staring](https://community.dhis2.org/u/Knut_Staring)\ **Post date:** [26 November 2015 22:01 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/6 "2015-11-26T22:01:27Z")
Thanks for the clarifications, Elmarie and Dipali I think these are good inputs to our dev team, and hope some of them will also chip into this conversation. Cheers, Knut ![image](https://dhis2.b-cdn.net/uploads/default/original/1X/7c66cee43ee6d15fecc7390e4389276b0a8abd75.png) > **···** > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] **On Behalf Of** Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max _\*_ _and try to select I can’t find anything and this field is_ > > > > - > > > - > > > > _mandatory and the data aggregator data element and tracker data element both has sum aggregator_ > > > > - > > > - > > > > _Any idea about this_ > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > – > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org) ---
**Author:** ![Olav](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/0ea827/32.png) [@Olav](https://community.dhis2.org/u/Olav)\ **Post date:** [27 November 2015 07:22 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/7 "2015-11-27T07:22:43Z")
Hi, just wanted to add my voice in support for improving (e.g. making it faster and more reliable, supporting cohort analysis etc), rather than removing the aggregation query builder. While I think the program indicators are great, I think there are many cases where you would rather want to store the aggregate data. One case I think is highly relevant (in the sense that countries are doing it now) is where you have some facilities reporting via tracker and other on paper/aggregate, and you would want the data to end up in the same data elements/data sets. Olav > **···** > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > \ > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] \*\*On Behalf Of \*\*Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max \_\* \_\*and try to select I can’t find anything and this field is \* > > > > - > > > - > > > > \*mandatory and the data aggregator data element and tracker data element both has sum aggregator \* > > > > - > > > - > > > > \*Any idea about this \* > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org/) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > – > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org/) ---
**Author:** ![Ese\_Egerega1](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/e/87869e/32.png) [@Ese\_Egerega1](https://community.dhis2.org/u/Ese_Egerega1)\ **Post date:** [5 January 2016 11:21 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/8 "2016-01-05T11:21:50Z")
Dear All, Am riding on Nami’s question on aggregate query builder. I would like to know if there is a work around since the dropdown for “Data element for sum/average/min/max\*” is not working in dhis2.21\* We have a data element in tracker which records the quantity of drugs dispensed to patients during ANC visits. We would like to aggregate the total drug dispensed to a dataset so as to calculate things like maximum stock or quantity of drugs to order. Right now, am able to aggregate only the number of visits but not the actual drugs dispensed. I will appreciated any suggestions. Thanks, Ese > **···** > > On Fri, Nov 27, 2015 at 8:22 AM, Olav Poppe [olav.poppe@me.com](mailto:olav.poppe@me.com) wrote: > > > Hi, > > just wanted to add my voice in support for improving (e.g. making it faster and more reliable, supporting cohort analysis etc), rather than removing the aggregation query builder. While I think the program indicators are great, I think there are many cases where you would rather want to store the aggregate data. > > > > > > One case I think is highly relevant (in the sense that countries are doing it now) is where you have some facilities reporting via tracker and other on paper/aggregate, and you would want the data to end up in the same data elements/data sets. > > > > > > Olav > > > > > > > > > > > > > 1. nov. 2015 kl. 23.01 skrev Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com): > > > > Thanks for the clarifications, Elmarie and Dipali > > > > I think these are good inputs to our dev team, and hope some of them will also chip into this conversation. > > > > Cheers, > > > > Knut > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > \ > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] \*\*On Behalf Of \*\*Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max \_\* \_\*and try to select I can’t find anything and this field is \* > > > > - > > > - > > > > \*mandatory and the data aggregator data element and tracker data element both has sum aggregator \* > > > > - > > > - > > > > \*Any idea about this \* > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org/) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org/) > > – ---
**Author:** ![Fatemacse](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/f/6bbea6/32.png) [@Fatemacse](https://community.dhis2.org/u/Fatemacse)\ **Post date:** [13 March 2016 12:28 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/9 "2016-03-13T12:28:53Z")
Dear All, We are facing same problem create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element forsum/average/min/max \_\* \__and try to select I can’t find anything and this field is_ - - mandatory and the data aggregation data element and tracker data element both has sum aggregation _Actually we cant to count Total Number of live birth from tracker capture patient type data element._ _How can I do it in program indicator ?_ _Thanks_ _Regards_ _Fatema_ > **···** > > On Tue, Jan 5, 2016 at 5:21 PM, Ese Egerega [ese@cis.com.ng](mailto:ese@cis.com.ng) wrote: > > > Dear All, > > > Am riding on Nami’s question on aggregate query builder. > > > I would like to know if there is a work around since the dropdown for “Data element for sum/average/min/max\*” is not working in dhis2.21\* > > > We have a data element in tracker which records the quantity of drugs dispensed to patients during ANC visits. We would like to aggregate the total drug dispensed to a dataset so as to calculate things like maximum stock or quantity of drugs to order. > > > Right now, am able to aggregate only the number of visits but not the actual drugs dispensed. > > > I will appreciated any suggestions. > > > Thanks, > > > Ese > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > On Fri, Nov 27, 2015 at 8:22 AM, Olav Poppe [olav.poppe@me.com](mailto:olav.poppe@me.com) wrote: > > > Hi, > > just wanted to add my voice in support for improving (e.g. making it faster and more reliable, supporting cohort analysis etc), rather than removing the aggregation query builder. While I think the program indicators are great, I think there are many cases where you would rather want to store the aggregate data. > > > > > > One case I think is highly relevant (in the sense that countries are doing it now) is where you have some facilities reporting via tracker and other on paper/aggregate, and you would want the data to end up in the same data elements/data sets. > > > > > > Olav > > > > > > > > > > > > > 1. nov. 2015 kl. 23.01 skrev Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com): > > > > Thanks for the clarifications, Elmarie and Dipali > > > > I think these are good inputs to our dev team, and hope some of them will also chip into this conversation. > > > > Cheers, > > > > Knut > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > \ > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] \*\*On Behalf Of \*\*Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max \_\* \_\*and try to select I can’t find anything and this field is \* > > > > - > > > - > > > > \*mandatory and the data aggregator data element and tracker data element both has sum aggregator \* > > > > - > > > - > > > > \*Any idea about this \* > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org/) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org/) > > – > > Regards, > > **Fatema Uddin** > > Technical Advisor (HIS)-Support to the Health Sector Program II > > Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH > > German House > > House 10/A-2, Road 90 > > Gulshan 2, Dhaka 1212, Bangladesh > > T +880 9666701000 > > F +880 28823099 > > M +880 19 6206 4712 > > I [www.giz.de](https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f) ---
**Author:** ![Lars](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/lars/32/34166_2.png) [@Lars](https://community.dhis2.org/u/Lars)\ **Post date:** [14 March 2016 16:40 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/10 "2016-03-14T16:40:40Z")
Hi there, in fact, aggregation query builder is already scheduled to be removed in version 2.23 and will be replaced by program indicators. Admittedly there are a few cases where persisted aggregate event data could be useful, however we think that generating the aggregate values “on the fly” will provide more flexibility and be less of a load on the server by avoiding large, nightly batch jobs. The current aggregation query builder was not up to scratch and could have unpredictable behavior. At some point, we might use the program indicator engine as basis for supporting persistence of aggregated event data, but that is not top priority currently. We encourage you to try out the program indicators and provide feedback to the dev team. regards, Lars > **···** > > On Sun, Mar 13, 2016 at 1:28 PM, Uddin Fatema [fatemacse@gmail.com](mailto:fatemacse@gmail.com) wrote: > > > Dear All, > > > We are facing same problem create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element forsum/average/min/max \_\* \__and try to select I can’t find anything and this field is_ > > > - > > - > > > mandatory and the data aggregation data element and tracker data element both has sum aggregation > > > _Actually we cant to count Total Number of live birth from tracker capture patient type data element._ > > > _How can I do it in program indicator ?_ > > > _Thanks_ > > > _Regards_ > > > _Fatema_ > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > On Tue, Jan 5, 2016 at 5:21 PM, Ese Egerega [ese@cis.com.ng](mailto:ese@cis.com.ng) wrote: > > > Dear All, > > > Am riding on Nami’s question on aggregate query builder. > > > I would like to know if there is a work around since the dropdown for “Data element for sum/average/min/max\*” is not working in dhis2.21\* > > > We have a data element in tracker which records the quantity of drugs dispensed to patients during ANC visits. We would like to aggregate the total drug dispensed to a dataset so as to calculate things like maximum stock or quantity of drugs to order. > > > Right now, am able to aggregate only the number of visits but not the actual drugs dispensed. > > > I will appreciated any suggestions. > > > Thanks, > > > Ese > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > Regards, > > **Fatema Uddin** > > Technical Advisor (HIS)-Support to the Health Sector Program II > > Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH > > German House > > House 10/A-2, Road 90 > > Gulshan 2, Dhaka 1212, Bangladesh > > T +880 9666701000 > > F +880 28823099 > > M +880 19 6206 4712 > > I [www.giz.de](https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f) > > On Fri, Nov 27, 2015 at 8:22 AM, Olav Poppe [olav.poppe@me.com](mailto:olav.poppe@me.com) wrote: > > > Hi, > > just wanted to add my voice in support for improving (e.g. making it faster and more reliable, supporting cohort analysis etc), rather than removing the aggregation query builder. While I think the program indicators are great, I think there are many cases where you would rather want to store the aggregate data. > > > > > > One case I think is highly relevant (in the sense that countries are doing it now) is where you have some facilities reporting via tracker and other on paper/aggregate, and you would want the data to end up in the same data elements/data sets. > > > > > > Olav > > > > > > > > > > > > > 1. nov. 2015 kl. 23.01 skrev Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com): > > > > Thanks for the clarifications, Elmarie and Dipali > > > > I think these are good inputs to our dev team, and hope some of them will also chip into this conversation. > > > > Cheers, > > > > Knut > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > \ > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] \*\*On Behalf Of \*\*Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max \_\* \_\*and try to select I can’t find anything and this field is \* > > > > - > > > - > > > > \*mandatory and the data aggregator data element and tracker data element both has sum aggregator \* > > > > - > > > - > > > > \*Any idea about this \* > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org/) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org/) > > – > > Lars Helge Øverland > > Lead developer, DHIS 2 > > University of Oslo > > Skype: larshelgeoverland > > [http://www.dhis2.org](https://www.dhis2.org/) ---
**Author:** ![Dipali\_Sahu](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/dipali_sahu/32/29476_2.png) [@Dipali\_Sahu](https://community.dhis2.org/u/Dipali_Sahu)\ **Post date:** [14 March 2016 18:43 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/11 "2016-03-14T18:43:54Z")
Hi Lars If not further work on the Aggregation query builder feature, please don’t remove it. We actually find it very useful and is critical to our workflow. For example, we need to get Total patients seen in a facility. Now with queries the user can see total figures, plus when needed segregate them by age, gender, visit type (new,follow up etc.) and many more categories in Pivot tables which provides a easy interface to suffice all this. Although it is possible to get these values through Program indicators, there is no flexibility to see data by a certain category like with the features that pivot table allow. I have read other emails on the group that Aggregation query builder’s behavior is a bit unpredictable, but I haven’t been able to produce any errors yet, given we have heavy database and complicated queries. Is it not possible to keep the Aggregation query feature as it is. Thank you Deepali > **···** > > On Mon, Mar 14, 2016 at 10:10 PM, Lars Helge Øverland [lars@dhis2.org](mailto:lars@dhis2.org) wrote: > > > Hi there, > > > in fact, aggregation query builder is already scheduled to be removed in version 2.23 and will be replaced by program indicators. Admittedly there are a few cases where persisted aggregate event data could be useful, however we think that generating the aggregate values “on the fly” will provide more flexibility and be less of a load on the server by avoiding large, nightly batch jobs. > > > > > > The current aggregation query builder was not up to scratch and could have unpredictable behavior. At some point, we might use the program indicator engine as basis for supporting persistence of aggregated event data, but that is not top priority currently. > > > > > > We encourage you to try out the program indicators and provide feedback to the dev team. > > > > > > regards, > > > > > > Lars > > > > > > > > > > > > > > > > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Sun, Mar 13, 2016 at 1:28 PM, Uddin Fatema [fatemacse@gmail.com](mailto:fatemacse@gmail.com) wrote: > > > Dear All, > > > We are facing same problem create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element forsum/average/min/max \_\* \__and try to select I can’t find anything and this field is_ > > > - > > - > > > mandatory and the data aggregation data element and tracker data element both has sum aggregation > > > _Actually we cant to count Total Number of live birth from tracker capture patient type data element._ > > > _How can I do it in program indicator ?_ > > > _Thanks_ > > > _Regards_ > > > _Fatema_ > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > Lars Helge Øverland > > Lead developer, DHIS 2 > > University of Oslo > > Skype: larshelgeoverland > > [http://www.dhis2.org](https://www.dhis2.org/) > > On Tue, Jan 5, 2016 at 5:21 PM, Ese Egerega [ese@cis.com.ng](mailto:ese@cis.com.ng) wrote: > > > Dear All, > > > Am riding on Nami’s question on aggregate query builder. > > > I would like to know if there is a work around since the dropdown for “Data element for sum/average/min/max\*” is not working in dhis2.21\* > > > We have a data element in tracker which records the quantity of drugs dispensed to patients during ANC visits. We would like to aggregate the total drug dispensed to a dataset so as to calculate things like maximum stock or quantity of drugs to order. > > > Right now, am able to aggregate only the number of visits but not the actual drugs dispensed. > > > I will appreciated any suggestions. > > > Thanks, > > > Ese > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > Regards, > > **Fatema Uddin** > > Technical Advisor (HIS)-Support to the Health Sector Program II > > Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH > > German House > > House 10/A-2, Road 90 > > Gulshan 2, Dhaka 1212, Bangladesh > > T +880 9666701000 > > F +880 28823099 > > M +880 19 6206 4712 > > I [www.giz.de](https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f) > > On Fri, Nov 27, 2015 at 8:22 AM, Olav Poppe [olav.poppe@me.com](mailto:olav.poppe@me.com) wrote: > > > Hi, > > just wanted to add my voice in support for improving (e.g. making it faster and more reliable, supporting cohort analysis etc), rather than removing the aggregation query builder. While I think the program indicators are great, I think there are many cases where you would rather want to store the aggregate data. > > > > > > One case I think is highly relevant (in the sense that countries are doing it now) is where you have some facilities reporting via tracker and other on paper/aggregate, and you would want the data to end up in the same data elements/data sets. > > > > > > Olav > > > > > > > > > > > > > 1. nov. 2015 kl. 23.01 skrev Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com): > > > > Thanks for the clarifications, Elmarie and Dipali > > > > I think these are good inputs to our dev team, and hope some of them will also chip into this conversation. > > > > Cheers, > > > > Knut > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > \ > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] \*\*On Behalf Of \*\*Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max \_\* \_\*and try to select I can’t find anything and this field is \* > > > > - > > > - > > > > \*mandatory and the data aggregator data element and tracker data element both has sum aggregator \* > > > > - > > > - > > > > \*Any idea about this \* > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org/) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org/) > > – ---
**Author:** ![Hannan](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/hannan/32/27159_2.png) [@Hannan](https://community.dhis2.org/u/Hannan)\ **Post date:** [27 March 2016 06:47 UTC](https://community.dhis2.org/t/aggregation-query-builder-details/3067/12 "2016-03-27T06:47:11Z")
Dear Lars I am agreed with Dipali and like her we are using aggregated query builder to populate aggregated data sets. So, please consider to keep this in future releases until and unless we find a suitable alternative. Regards Hannan Khan HISP Bangladesh > **···** > > On Tue, Mar 15, 2016 at 12:43 AM, Dipali Sahu [dipali.sahu@gmail.com](mailto:dipali.sahu@gmail.com) wrote: > > > Hi Lars > > > > > > If not further work on the Aggregation query builder feature, please don’t remove it. We actually find it very useful and is critical to our workflow. For example, we need to get Total patients seen in a facility. Now with queries the user can see total figures, plus when needed segregate them by age, gender, visit type (new,follow up etc.) and many more categories in Pivot tables which provides a easy interface to suffice all this. > > > > > > Although it is possible to get these values through Program indicators, there is no flexibility to see data by a certain category like with the features that pivot table allow. > > > > > > I have read other emails on the group that Aggregation query builder’s behavior is a bit unpredictable, but I haven’t been able to produce any errors yet, given we have heavy database and complicated queries. > > > > > > Is it not possible to keep the Aggregation query feature as it is. > > > > > > Thank you > > > Deepali > > > > > > > > > > > > > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Mon, Mar 14, 2016 at 10:10 PM, Lars Helge Øverland [lars@dhis2.org](mailto:lars@dhis2.org) wrote: > > > Hi there, > > > in fact, aggregation query builder is already scheduled to be removed in version 2.23 and will be replaced by program indicators. Admittedly there are a few cases where persisted aggregate event data could be useful, however we think that generating the aggregate values “on the fly” will provide more flexibility and be less of a load on the server by avoiding large, nightly batch jobs. > > > > > > The current aggregation query builder was not up to scratch and could have unpredictable behavior. At some point, we might use the program indicator engine as basis for supporting persistence of aggregated event data, but that is not top priority currently. > > > > > > We encourage you to try out the program indicators and provide feedback to the dev team. > > > > > > regards, > > > > > > Lars > > > > > > > > > > > > > > > > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Sun, Mar 13, 2016 at 1:28 PM, Uddin Fatema [fatemacse@gmail.com](mailto:fatemacse@gmail.com) wrote: > > > Dear All, > > > We are facing same problem create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element forsum/average/min/max \_\* \__and try to select I can’t find anything and this field is_ > > > - > > - > > > mandatory and the data aggregation data element and tracker data element both has sum aggregation > > > _Actually we cant to count Total Number of live birth from tracker capture patient type data element._ > > > _How can I do it in program indicator ?_ > > > _Thanks_ > > > _Regards_ > > > _Fatema_ > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > > Lars Helge Øverland > > Lead developer, DHIS 2 > > University of Oslo > > Skype: larshelgeoverland > > [http://www.dhis2.org](https://www.dhis2.org/) > > On Tue, Jan 5, 2016 at 5:21 PM, Ese Egerega [ese@cis.com.ng](mailto:ese@cis.com.ng) wrote: > > > Dear All, > > > Am riding on Nami’s question on aggregate query builder. > > > I would like to know if there is a work around since the dropdown for “Data element for sum/average/min/max\*” is not working in dhis2.21\* > > > We have a data element in tracker which records the quantity of drugs dispensed to patients during ANC visits. We would like to aggregate the total drug dispensed to a dataset so as to calculate things like maximum stock or quantity of drugs to order. > > > Right now, am able to aggregate only the number of visits but not the actual drugs dispensed. > > > I will appreciated any suggestions. > > > Thanks, > > > Ese > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > – > Regards, > > **Fatema Uddin** > > Technical Advisor (HIS)-Support to the Health Sector Program II > > Deutsche Gesellschaft für Internationale Zusammenarbeit (GIZ) GmbH > > German House > > House 10/A-2, Road 90 > > Gulshan 2, Dhaka 1212, Bangladesh > > T +880 9666701000 > > F +880 28823099 > > M +880 19 6206 4712 > > I [www.giz.de](https://owa.giz.de/owa/redir.aspx?C=mPQMujasE0-yL6Aa4ocPcnkcfUThJNEIi4h1DwsYjO3Ua20UR_COt6aTH1s4JIdR4p-jCoFsP6A.&URL=http%3a%2f%2fwww.giz.de%2f) > > On Fri, Nov 27, 2015 at 8:22 AM, Olav Poppe [olav.poppe@me.com](mailto:olav.poppe@me.com) wrote: > > > Hi, > > just wanted to add my voice in support for improving (e.g. making it faster and more reliable, supporting cohort analysis etc), rather than removing the aggregation query builder. While I think the program indicators are great, I think there are many cases where you would rather want to store the aggregate data. > > > > > > One case I think is highly relevant (in the sense that countries are doing it now) is where you have some facilities reporting via tracker and other on paper/aggregate, and you would want the data to end up in the same data elements/data sets. > > > > > > Olav > > > > > > > > > > > > > 1. nov. 2015 kl. 23.01 skrev Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com): > > > > Thanks for the clarifications, Elmarie and Dipali > > > > I think these are good inputs to our dev team, and hope some of them will also chip into this conversation. > > > > Cheers, > > > > Knut > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > On Fri, Nov 27, 2015 at 4:11 AM, Elmarie Claasen [elmarie@hisp.org](mailto:elmarie@hisp.org) wrote: > > > Hi Knut, > > > > > > Although I can see that program indicators can be helpful it has limited use in the context of interoperability between DHIS2 instances. In South Africa we feel that Survey type instances should be kept separate from Routine Health data collection and as such collect event or tracker data for surveys and would want to set up aggregated data elements in the system which populates with a stored aggregated data value which we export/synchronise with the routine HMIS system e.g. > > > Patient satisfaction survey rate = No of surveys conducted/No of facilities > > > Patient satisfaction rate = No of satisfied responses/All responses > > > > > > It is true that these aggregated elements can be populated with a function called via a sql view or cron job but not the most ideal. > > > Up to now I have not figured out how to set up an indicator which counts if there was any surveys done in a specific period by adding a program indicator due to the limited amount of mathematical calculation formulae allowed in the Program Indicator engine. > > > > > > This said I must say that we don’t have much confidence in the accuracy of the Aggregation Query Builder as it is at the moment because there seems to be a ceiling limit on how many values can actually be inserted by running a scheduled or manual aggregation process and the simultaneous initiation of scheduled Aggregation Query builder and Analytics are problematic. For this reason we are checking the output of the Aggregation Query builder at this stage and when not entirely confident that it produces reliable results we are using functions instead. > > > > > > Regards, > > > > > > **Elmarie Claasen** > > > \ > > > Project Manager > > > Health Information Systems Program > > > Tel: 041-367 1027 > > > Cell: 082 374 2209 > > > E-mail: [elmarie@hisp.org](mailto:elmarie@hisp.org) > > > Skype: elmarie.claasen52 > > > > > > > > > This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. > > If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer. > > > > > > **From:** Dhis2-users [[mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net](mailto:dhis2-users-bounces+elmarie=hisp.org@lists.launchpad.net)] \*\*On Behalf Of \*\*Dipali Sahu > > **Sent:** 26 November 2015 12:49 PM > > **To:** Knut Staring > > **Cc:** DHIS 2 Users list > > **Subject:** Re: [Dhis2-users] Aggregation query builder details > > > > > > Hi Knut > > > > > > We actually use Aggregation query builder to a large extent to get aggregated data. Exclusively, all our data is captured through Event capture, and we aggregate it through Aggregation query builder, so that the aggregated values can be shown in datasets as well as Pivot tables. > > > Also, using Pivot tables with data elements is easier for the end users since they there is flexibility to can slice and dice the data with categories under Pivot tables. > > > > > > Don’t you think aggregation with only program indicators will limit the use of categories and therefore easy visualization under pivot tables. > > > > > > Thank you > > > Deepali > > > International Medical Corps > > > > > > > > > On Wed, Nov 25, 2015 at 11:34 AM, Knut Staring [knutst@gmail.com](mailto:knutst@gmail.com) wrote: > > > Hi Nami, > > > > > > Which version of DHIS2 are you working on? We are phasing out the aggregation query builder in favor of program indicators. > > > > > > Knut > > > > > > On Tue, Nov 24, 2015 at 5:45 PM, Nami Ghadri [nami\_ghadri@hotmail.com](mailto:nami_ghadri@hotmail.com) wrote: > > > > Hello, > > > > > > > > I want to create an Aggregation query builder details as sum of data element value I put everything but it comes to Data element for sum/average/min/max \_\* \_\*and try to select I can’t find anything and this field is \* > > > > - > > > - > > > > \*mandatory and the data aggregator data element and tracker data element both has sum aggregator \* > > > > - > > > - > > > > \*Any idea about this \* > > > > - > > > - > > > > _Thanks in advance_ > > > > > > > > > > > > * * * > > > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > > > > – > > > Knut Staring > > > Dept. of Informatics, University of Oslo > > > Norway: +4791880522 > > > Skype: knutstar > > > [http://dhis2.org](http://dhis2.org/) > > > * * * > > > > Mailing list: [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net) > > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users) > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp) > > > > > > _This message and any attachments are subject to a disclaimer published at [http://www.hisp.org/policies.html#comms\_disclaimer](http://www.hisp.org/policies.html#comms_disclaimer). Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to [disclaimer@hisp.org](mailto:disclaimer@hisp.org) and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer._ > > Knut Staring > > Dept. of Informatics, University of Oslo > > Norway: +4791880522 > > Skype: knutstar > > [http://dhis2.org](http://dhis2.org/) > > –