Total in procent

[johnan]

Hi,

I have data:

I

ID	LINE	DATE	Collis
131	501	2018-09-25	67
145	501	2018-09-25	40
123	702	2018-09-25	20
99	702	2018-09-25	50
87	105	2018-09-25	73

Ok, i know that the sum is 107 of collis per Line.

Now i want to show in a new column the procent of each Line. after collis

Like this:

67/107 Givs 62,62%

40/107 givs 37,38%

and so on.

The last post gives me 100% it's only one

The collis are count(ColliNbr)

I have tried aggr ,but i can't get it right.

[dplr-rn]

assuming collis expression is sum(value)

you can add a new measure sum( TOTAL value)

see below

What does the TOTAL qualifier do?

[johnca]

Obviously, there is much more data than this to consider.

But, it seems you are desiring to sum Collis grouping by LINE and/or DATE yet not ID to get your denominator, then divide the Collis into the sum. Is that correct? Please clarify.

V/r,

John

[johnan]

Sorry it must be by LINE and Date. I have updated the post above

[johnan]

But i have to count total grouping by LINE

If i add Total it gives me 250 on each row.

[johnca]

Try this...

data:

load * inline [

ID, LINE, DATE, Collis

131, 501, 2018-09-25, 67

145, 501, 2018-09-25, 40

123, 702, 2018-09-25, 20

99, 702, 2018-09-25, 50

87, 105, 2018-09-25, 73

];

Left Join(data)

load

LINE,

DATE,

Sum(Collis) as Collis_Sum

Resident data

Group By

LINE,

DATE;

final:

NoConcatenate

load

ID,

LINE,

DATE,

Collis,

Collis / Collis_Sum as Percent

Resident data;

drop table data;

[dplr-rn]

Missed that part.

whats the aggr function you are using? and the output

[johnan]

I like it, but the data is  coming from AS400 and the script is SQL based. I cannot use inline in this part. Sorry mate,

[johnca · Accepted solution]

Simple enough, mate...the script is similar. Just change the initial data table with your sql;

data:

Select

     ID,

     LINE,

     DATE,

     Collis

From your_as400_database;

Then add the following;

Left Join(data)

load

LINE,

DATE,

Sum(Collis) as Collis_Sum

Resident data

Group By

LINE,

DATE;

final:

NoConcatenate

load

ID,

LINE,

DATE,

Collis,

Collis / Collis_Sum as Percent

Resident data;

drop table data;

[johnan]

Thats will doit!!! worked 100% Thanx mate!!