Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
johnan
Creator III
Creator III

Total in procent

Hi,

I have data:

I

IDLINEDATECollis
1315012018-09-2567
1455012018-09-2540
1237022018-09-2520
997022018-09-2550
871052018-09-2573

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.

1 Solution

Accepted Solutions
johncaqc
Specialist
Specialist

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;

View solution in original post

9 Replies
dilipranjith
Partner
Partner

assuming collis expression is sum(value)

you can add a new measure sum( TOTAL value)

see below

What does the TOTAL qualifier do?

johncaqc
Specialist
Specialist

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
Creator III
Creator III
Author

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

johnan
Creator III
Creator III
Author

But i have to count total grouping by LINE

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

johncaqc
Specialist
Specialist

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;


Thread 315040.png

dilipranjith
Partner
Partner

Missed that part.

whats the aggr function you are using? and the output

johnan
Creator III
Creator III
Author

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,

johncaqc
Specialist
Specialist

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;

View solution in original post

johnan
Creator III
Creator III
Author

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