Qlik Community

Qlik Sense App Development

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

wenjun2016
New Contributor

How to count number of not Null values in a row

SeptOctNovDecAverage
332(3+3+2+0)/3
2541(2+5+4+1)/4
34(3+4+0+0)/2

How to count number of values across 4 data columns, excluding null values?

Thank you.

6 Replies
MVP
MVP

Re: How to count number of not Null values in a row

RangeSum(IF(Len(Trim(Sept))<>0,1),IF(Len(Trim(Oct))<>0,1),IF(Len(Trim(Nov))<>0,1),IF(Len(Trim(Dec))<>0,1))

surendraj
Valued Contributor

Re: How to count number of not Null values in a row

Hi,

With reference to manish kachhia

Try this

RangeSum(Sept,Oct,Nov,Dec)/RangeSum(IF(Len(Trim(Sept))<>0,1),IF(Len(Trim(Oct))<>0,1),IF(Len(Trim(Nov))<>0,1),IF(Len(Trim(Dec))<>0,1))

it works!!

--Surendra

anushree1
Contributor III

Re: How to count number of not Null values in a row

Is there a way to do this dynamically so that in case new columns are added the expression need not be changed each time?

wenjun2016
New Contributor

Re: How to count number of not Null values in a row

Hi Surendra and Manish,

Thank you for your reply.

It works.

Ya I have the same question with Anushree below.

When data months are added, we have to change the expression again, is there a way like count dynamically?

Thanks!

michielvandegoo
Valued Contributor

Re: How to count number of not Null values in a row

For starters you could use 2 range functions for your expression:

rangesum(Sept, Oct, Nov,Dec) / rangenumericccount(Sept, Oct, Nov,Dec)

This eliminates the LEN and Trim functions.

For the next part please refer to this post: Re: Create script expression from variable no of fields

wenjun2016
New Contributor

Re: How to count number of not Null values in a row

Thanks! RangeNumericCount() function shorten by alot !

Community Browser