How to Exclude Null Values from Calculation

[Anonymous]

I am using the Simple KPI extension.

My current syntax aims to count the Number of responses that score above 4 on a scale between 1 and 5. It also aims to only draw from the 4th quarter of 2017.

It seems to be calculating correctly. It is just also calculating the #NULL values from the Excel spreadsheet. What is the best way to avoid this?

Syntax below:

Num(Count({<test_measure = {">=4"}>} {<[Admin_Qtr]={"Q4 2017"}>} test_measure) /Count(test_measure),'0%')

[sunny_talwar]

It is counting Null? But count function don't count null.... may be there are white spaces... try this

Num(Count({<test_measure = {">=4"}, [Admin_Qtr]={"Q4 2017"}, test_measure = {"=Len(Trim(test_measure)) > 0"}>} test_measure) /Count({<test_measure = {"=Len(Trim(test_measure)) > 0"}>} test_measure),'0%')

[OmarBenSalem]

sunny; I know this works as an and :

test_measure = {">=4"}, test_measure = {"=Len(Trim(test_measure)) > 0"}

But, what if I wanted to write both condition within the same {} ? how should it be written?

I mean sthig like :

test_measure = {">=4  and =Len(Trim(test_measure)) > 0"}

Is sthing like this possible?

[Anonymous]

Ok, I tried that and it didn't work. So what I am thinking is that my initial line was not correct.

So what I am now thinking is that this:

Num(Count({<test_measure = {">=4"}>} {<[Admin_Qtr]={"Q4 2017"}>} test_measure) /Count(test_measure),'0%')

Was just giving me back the percentage of Q4 2017. It was shooting back about 25% so that would make sense as there are 4 quarters and all.

I did not get any errors in my syntax on Qlik Sense. So what am I doing incorrectly in that?

[sunny_talwar]

Oh... didn't realize that there was already a set analysis on test measure... I would do this

test_measure = {"=test_measure >= 4  and =Len(Trim(test_measure)) > 0"}

What I gave initially is probably not going to work

[Anonymous]

I think you were right in that it wasn't counting the Nulls. I just am not sure how to get it to do both things (above 4 and Q4 2017 quarter)...so it is just doing the Quarter count it looks like.

[sunny_talwar]

How about this

Num(Count({<test_measure = {">=4"}, [Admin_Qtr]={"Q4 2017"}>} test_measure) /Count(test_measure),'0%')

[Anonymous · Accepted solution]

This ended up working....

Num(Count({<test_measure = {">=4"}, Admin_Qtr={"Q4 2017"} >} test_measure) /Count({<Admin_Qtr={"Q4 2017"} >} test_measure),'0%')

[sunny_talwar]

Awesome, please close the thread by marking correct and helpful responses.

Best,

Sunny