Problem with dbr.avg

(8 posts) (2 voices)
  • Started 6 years ago by fxnaranjo
  • Latest reply from fxnaranjo

Tags:

  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,

    Posted 6 years ago #
  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

    Posted 6 years ago #
  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.

    Posted 6 years ago #
  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

    Posted 6 years ago #
  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.

    Posted 6 years ago #
  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

    Posted 6 years ago #
  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

    Posted 6 years ago #
  8. fxnaranjo, Member

    Ok, thanks for your help.!!

    Posted 6 years ago #

Reply

You must log in to post.