topic Re: Calculating average in each row of table in App Development

Calculating average in each row of table

stxphxe — Sat, 16 Nov 2024 20:48:48 GMT

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

Re: Calculating average in each row of table

Mauritz_SA — Thu, 23 May 2019 06:44:55 GMT

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/RangeFunctions/rangeavg.htm

Good luck!

Mauritz

Re: Calculating average in each row of table

stxphxe — Thu, 23 May 2019 10:15:00 GMT

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?

Re: Calculating average in each row of table

Mauritz_SA — Thu, 23 May 2019 11:15:24 GMT

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

Re: Calculating average in each row of table

stxphxe — Fri, 24 May 2019 14:52:26 GMT

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

Re: Calculating average in each row of table

Mauritz_SA — Mon, 27 May 2019 07:13:59 GMT

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