Qlik Community

Qlik Sense App Development

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

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
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!