Calculating percentages in the 'Total' column

(10 posts) (2 voices)


No tags yet.

  1. vannc, Member

    Dear Team!

    I am struggling with calculating percentages in the 'Total' column. I have done with 'dbr.hsum' to calculate the sum, but I am having trouble with percentages. :(
    this is my code:
    select 'dbr.summary.text', 'Total';
    select 'dbr.hsum', 'ty';
    --select 'dbr.havg', '%Actual';
    select 'dbr.hsum', 'Plan';
    --select 'dbr.havg', '%Plan';
    --select 'dbr.havg', '%';
    select 'dbr.hsum', 'MoM';
    --select 'dbr.havg', '%MoM';
    select 'dbr.hsum', 'ly';
    --select 'dbr.havg', '%YoY';
    --select 'dbr.summary.calc', '%', 'ty/Plan*100';
    --select 'dbr.summary.calc', '%MoM', '(ty/MoM-1) * 100';
    --select 'dbr.summary.calc', '%YoY', '(ty/ly-1) * 100';

    Thanks Team!

  2. myDBR Team, Key Master

    The question contains no no query so it would be a quess what you are trying to do.

    Couple of notes:

    • You can use dbr.calc instead of dbr.summary.calc. The dbr.summary.calc is kept just for compatibility for old versions.
    • See the manual for dbr.calc usage, i.e. use brackets around column references

    myDBR Team

  3. vannc, Member

    Dear Team!

    Thanks for your note. I will use dbr.calc. But percentages are not working for me.
    This is the full source code:
    And this is picture of total column

    Thanks Team!

  4. myDBR Team, Key Master

    When doing calculations, the formula is applied to all columns where the value is NULL. In your code you have the only calculation on the %YoY-column where you calculate the value already by yourself.

    myDBR Team

  5. vannc, Member

    Dear Team,

    So you mean that is the reason why the percentage column is hidden in the 'Total' group?

  6. myDBR Team, Key Master

    The crosstab horizontal summary columns show up on those columns you have defined the horizontal actions: In your case the columns defined by the dbr.hsum-command.

    In order to add the %YoY-column to be added and in order to keep the NULL value for calculation, use dbr.hnull-command for the column.

    myDBR Team

  7. vannc, Member

    Dear Team!

    Wow, finally, the percentage columns show up. Thanks team! :D

    Best regards:

  8. vannc, Member

    Dear Team!

    WHEN (sum(b.recent) / sum(b.[20240331HDQTV1]) * 100) >= 100 THEN 'color:green;'
    ELSE 'color:red;'
    END AS 'a_style',

    But how can I apply this CSS for '%' column in the 'Total' group?

    select 'dbr.calc', '%', '[ty]/[Plan]*100';

  9. myDBR Team, Key Master

    See the documentation. The dbr.calc command has the summary_format-option uses the same format as dbr.colstyle.

    myDBR Team

  10. vannc, Member

    Thanks team, I followed your instruction another topic; this is about



You must log in to post.