Qlik Community

Qlik Sense App Development

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

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!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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

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

2 Replies
MVP
MVP

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

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

Not applicable

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

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!