topic Re: Total in procent in QlikView

Total in procent

johnan — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have data:

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.

Re: Total in procent

dplr-rn — Tue, 25 Sep 2018 18:14:57 GMT

assuming collis expression is sum(value)

you can add a new measure sum( TOTAL value)

see below

What does the TOTAL qualifier do?

Re: Total in procent

johnca — Tue, 25 Sep 2018 18:15:11 GMT

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

Re: Total in procent

johnan — Tue, 25 Sep 2018 18:20:17 GMT

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

Re: Total in procent

johnan — Tue, 25 Sep 2018 18:25:23 GMT

But i have to count total grouping by LINE

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

Re: Total in procent

johnca — Tue, 25 Sep 2018 18:25:55 GMT

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;

Re: Total in procent

dplr-rn — Tue, 25 Sep 2018 18:27:54 GMT

Missed that part.

whats the aggr function you are using? and the output

Re: Total in procent

johnan — Tue, 25 Sep 2018 18:36:39 GMT

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,

Re: Total in procent

johnca — Tue, 25 Sep 2018 18:40:52 GMT

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;

Re: Total in procent

johnan — Tue, 25 Sep 2018 18:53:31 GMT

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