Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how do i go about calculating the average of each row in this 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.
If you are still having trouble then you can share your sample app and I can try to help.
Regards,
Mauritz
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
Good luck!
Mauritz
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?
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
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
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