Qlik Community

Qlik Sense App Development

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

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to calculate the average of different fields with incidental null values??

Hi!

I have a table with 7 fields (columns) which correspond to questions in a survey. It's a bit like this:

1. To what extent do you agree with [statement 1]?

2. To what extent do you agree with [statement 2]?

3. To what extent do you agree with [statement 3]?

...

7. To what extent do you agree with [statement 7]?

The possible values ("the scores") for each field range from 1 to 5, but null values ("N/A") also occur. I would like to calculate the average of these fields per row. That means I have to sum the values and divide them by the number of fields which do not have a null value.

What would be the best solution?

Thanks!

1 Solution

Accepted Solutions
Highlighted

You can use RangeAvg() or RangeSum() which ignore text and null values

RangeAvg(Field1, Field2, Field3, Field4,.... Field7) as Avg

or

RangeSum(Field1, Field2, Field3, Field4,.... Field7)/7 as Avg

View solution in original post

2 Replies
Highlighted

You can use RangeAvg() or RangeSum() which ignore text and null values

RangeAvg(Field1, Field2, Field3, Field4,.... Field7) as Avg

or

RangeSum(Field1, Field2, Field3, Field4,.... Field7)/7 as Avg

View solution in original post

Highlighted
Not applicable

Yes!

You're first solution is what I was looking for. The second one requires a fixed number (7) of fields, but because of the null values some fields don't count.

Thank you, Sunny!