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

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
------------------------------

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:
Sent: Mon October 24, 2022 03:41 PM
From: mark kertzner
Subject: how can I get the accumulated percentage by column

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
------------------------------

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:
Sent: Wed October 26, 2022 10:43 AM
From: rosario scarlett prado
Subject: how can I get the accumulated percentage by column

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:
Sent: Mon October 24, 2022 03:41 PM
From: mark kertzner
Subject: how can I get the accumulated percentage by column

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