Calculating average in each row of table

[stxphxe]

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

[Mauritz_SA]

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]

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]

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]

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

[Mauritz_SA · Accepted solution]

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.

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

Regards,

Mauritz