1. 

   fxnaranjo, Member

   Hi,

   I anm using the dbr.avg function but the calculation is ignoring the values containing zero(0). How can I fix this?

   Thanks,
2. 

   myDBR Team, Key Master

   Hi,
   zeros are included in the average calculation. There are couple of things that you might want to check.

   Check that your data does not include NULL's as NULL's are not included in the aggregate calculations. If you wish to treat NULL's as zeros, use ifnull, isnull functions.

   Another thing that may affect the calculation is the datatype. On integer values the end result is, buy default, also an integer, so the average calculation gets rounded to the nearest integer.

   --
   myDBR Team
3. 

   fxnaranjo, Member

   Hi this is the code is not working

   select 'dbr.sum',5,6,7,8,9,10;
   select 'dbr.calc', 'percent','[a1]+[a2]+[a3]+[a4]+[a5]+[a6]';
   select 'dbr.crosstab', 4;
   select 'dbr.calc', 'p1','([a1]/[percent])*100';
   select 'dbr.calc', 'p2','([a2]/[percent])*100';
   select 'dbr.calc', 'p3','([a3]/[percent])*100';
   select 'dbr.calc', 'p4','([a4]/[percent])*100';
   select 'dbr.calc', 'p5','([a5]/[percent])*100';
   select 'dbr.calc', 'p6','([a6]/[percent])*100';

   select 'dbr.avg','[p1]';

   select
   Province as 'Provincia',
   District as 'Distrito',
   Corregimiento as 'Corregimiento',
   'Estado Civil',
   SUM(CASE WHEN CivilStatus= 'Casado/a' THEN 1 ELSE 0 END) as 'Casado/a[a1]',
   SUM(CASE WHEN CivilStatus= 'Soltero/a' THEN 1 ELSE 0 END) as 'Soltero/a[a2]',
   SUM(CASE WHEN CivilStatus= 'Viudo/a' THEN 1 ELSE 0 END) as 'Viudo/a[a3]',
   SUM(CASE WHEN CivilStatus= 'Divorciado/a' THEN 1 ELSE 0 END) as 'Divorciado/a[a4]',
   SUM(CASE WHEN CivilStatus= 'Unión libre' THEN 1 ELSE 0 END) as 'Unión libre[a5]',
   SUM(CASE WHEN CivilStatus= 'No Information' THEN 1 ELSE 0 END) as 'No Information[a6]',
   null as 'TOTAL[percent]',
   null as '% Casado/a[p1]',
   null as '% Soltero/a[p2]',
   null as '% Viudo/a[p3]',
   null as '% Divorciado/a[p4]',
   null as '% Unión libre[p5]',
   null as '% No Information[p6]'
   from replance_panamacensusnuevo.householdmaster h
   left join replance_panamacensusnuevo.householdfamilymembers f on h.HouseholdID=f.HouseholdID
   where (h.Proyecto in (select proyecto from table_proyecto) or h.Proyecto is null) and
   f.Relation='Responsable'
   group by 1,2,3;

   The average for [p1] is not including the zero values.
4. 

   myDBR Team, Key Master

   Hi,
   could you run the report in SQL Editor so that the "Output as SQL" checkbox is checked. Then send the output to support. If you can, try to keep the number of rows reasonable.

   --
   myDBR Team
5. 

   fxnaranjo, Member

   
   
   select 'dbr.sum',2,3;
   
   select 'dbr.calc', 'percent','[a1]+[a2]';
   
   select 'dbr.calc', 'p1','([a1]/[percent])*100';
   
   select 'dbr.calc', 'p2','([a2]/[percent])*100';
   
   select 'dbr.avg','[p1]';
   
   select
   
   Province as 'Provincia',
   
   SUM(CASE WHEN CivilStatus= 'Casado/a' THEN 1 ELSE 0 END) as 'Casado/a[a1]',
   
   SUM(CASE WHEN CivilStatus= 'Soltero/a' THEN 1 ELSE 0 END) as 'Soltero/a[a2]',
   
   null as 'TOTAL[percent]',
   
   null as '% Casado/a[p1]',
   
   null as '% Soltero/a[p2]',
   
   from replance_panamacensusnuevo.householdmaster h
   
   group by 1,2,3;
   
   The average for [p1] is not including the zero values. 
6. 

   myDBR Team, Key Master

   The dbr.avg is not supported currently on dbr.calc columns as by default the dbr.calc applies also to summary rows. Also, the dbr.calc column references are currently not supported on other dbr.calc columns.

   Both of these are in the todo list. We can take a look what can be done here.

   --
   myDBR Team
7. 

   myDBR Team, Key Master

   We've made some changes to dbr.calc functionality and added support for both referencing other calculation columns and to the aggregate functions.

   Run the updater to get the latest release.
   --
   myDBR Team
8. 

   fxnaranjo, Member

   Ok, thanks for your help.!!

Reply

You must log in to post.