Hi,
I anm using the dbr.avg function but the calculation is ignoring the values containing zero(0). How can I fix this?
Thanks,
Hi,
I anm using the dbr.avg function but the calculation is ignoring the values containing zero(0). How can I fix this?
Thanks,
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
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.
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
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.
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
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
Ok, thanks for your help.!!
You must log in to post.