Global AI and Data Science

 View Only

  • 1.  how can I get the accumulated percentage by column

    Posted Fri October 21, 2022 10:23 AM
    a query in a report in ibm cognos in a cross table how can I get the accumulated percentage by column example column A HAS A QUANTITY.. COLUMN B IS THE SUM OF THE QUANTITY OF COLUMN A PLUS THE QUANTITY OF COLUMN B AND SO ON                                                               


    Status         / COLUMN A / COLUMN B / COLUMN C
     Closed             10 /                     20 /            5
     Open               15 /                 10 /               10
     TOTAL            25 /                 30 /               15

     ACCUMULATED PERCENTAGE

     Status  / COLUMN A /    COLUMN B             / COLUMN C
     Closed             40% / (10+20/55)->  55%   /        50%
     Open               60% /            45% /                    50%
     TOTAL            100%  /           100% /                100%

    ------------------------------
    rosario scarlett prado
    ------------------------------

    #GlobalAIandDataScience
    #GlobalDataScience


  • 2.  RE: how can I get the accumulated percentage by column

    Posted Tue October 25, 2022 11:43 AM
    this works for three columns but will get unwieldy for more than that.

    create table tmp(
    Status varchar(10) not null,
    ColumnA float,
    ColumnB float,
    ColumnC float
    )

    insert into tmp (Status, ColumnA, ColumnB, ColumnC) values ('Closed', 10, 20, 5)
    insert into tmp (Status, ColumnA, ColumnB, ColumnC) values ('Open', 15, 10, 10)

    SELECT Status
    , round(ColumnA * 100 / tot.a, 0) AS '% of total'
    , round((ColumnA + ColumnB) * 100 / tot.b, 0) AS '% of total'
    , round((ColumnA + ColumnB + ColumnC) * 100 / tot.c, 0) AS '% of total'
    FROM tmp
    CROSS JOIN (
    SELECT SUM(ColumnA) AS a
    , SUM(ColumnA + ColumnB) AS b
    , SUM(ColumnA + ColumnB + ColumnC) AS c
    FROM tmp) tot

    drop table tmp

    ------------------------------
    mark kertzner
    ------------------------------

    Original Message


  • 3.  RE: how can I get the accumulated percentage by column

    Posted Wed October 26, 2022 10:43 AM
    i was using formula( running-total) but i need the results in percentage

    running-total ([Cantidad de Cierre] for [Estado Reclamo])

    ------------------------------
    rosario scarlett prado
    ------------------------------

    Original Message


  • 4.  RE: how can I get the accumulated percentage by column

    Posted Thu October 27, 2022 04:33 PM
    Hi Rosario,

    By my understanding, the running-total function works across rows of data (example).

    So in order to use this function you would need to transform your data into something like this

    Status,Column_Name,Value
    Closed,COLUMN A,10
    Closed,COLUMN B,20
    Closed,COLUMN C,5
    Open,COLUMN A,15
    Open,COLUMN B,10
    Open,COLUMN C,10

    and then process using running-total().

    BTW, it is challenging to extrapolate what you are attempting with

    running-total ([Cantidad de Cierre] for [Estado Reclamo])


    If you could relate this statement to the example you initially provided that would be easier for others to provide assistance.



    ------------------------------
    Steven Balzer
    ------------------------------

    Original Message