Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

stxphxe
New Contributor II

Calculating average in each row of table

how do i go about calculating the average of each row in this table?

h.png

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Mauritz_SA
Contributor III

Re: Calculating average in each row of table

Hi Steph

The code should essentially add a new column with the average for the row. Below is a screenshot of the sample app I created and the new column (with the average for the whole row) in yellow.

Average Column.PNG

If you are still having trouble then you can share your sample app and I can try to help.

Regards,

Mauritz

5 Replies
Mauritz_SA
Contributor III

Re: Calculating average in each row of table

Hi there

In the data manager (I can't get RangeAvg to work there for some reason):

-Add field

-Calculated field

-In the expression, add the values of your columns and divide by the number of columns ([Raw HRV] + Column1+ Column2+...+Column11)/12

-Give it a name and create.

In the load script:

Add the following field when you pull in the data and add all of the fields you want included in the calculation of the average in the brackets:

RangeAvg([Raw HRV], [Column1], [Column2]) AS Average

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/RangeFun...

Good luck!

Mauritz

stxphxe
New Contributor II

Re: Calculating average in each row of table

Thank you! I will try this out. I have about 166 columns is there a faster way to input this, rather than to type each column number?

Mauritz_SA
Contributor III

Re: Calculating average in each row of table

Hi there

The script below should give you what you need. Just update the path.

[Sheet2]:
LOAD
*
FROM [lib://Qlik Test Data Files/Test New Column.xlsx]
(ooxml, embedded labels, table is Sheet2);

ColumnNameTable:
CrossTable(ColumnName,Data,1)
Load *
Resident Sheet2;


LEFT JOIN ([Sheet2])
LOAD
Date,
Avg(Data) AS Average
RESIDENT
ColumnNameTable
GROUP BY Date;
DROP TABLE ColumnNameTable;

The 1 in the crosstable specifies the number of columns to treat as qualifying fields. In this case it will treat Date as a qualifying field and get the average of the rest regardless of how many columns you have.

Regards,

Mauritz 

stxphxe
New Contributor II

Re: Calculating average in each row of table

Hello,

Thank you this solution works but it averages by columns.

What I am after is to average by date, so to average each row is this possible?

Kind regards,

Steph

Highlighted
Mauritz_SA
Contributor III

Re: Calculating average in each row of table

Hi Steph

The code should essentially add a new column with the average for the row. Below is a screenshot of the sample app I created and the new column (with the average for the whole row) in yellow.

Average Column.PNG

If you are still having trouble then you can share your sample app and I can try to help.

Regards,

Mauritz