Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor III

Removing N/A's from a Table

Hi, I have a table with the measure $(fComplianceScore) which is calculating scores but I need to add for it to remove any total that is 0% or N/A. How can I do that?

Thank you!

1 Solution

Accepted Solutions
arulsettu
Honored Contributor III

Re: Removing N/A's from a Table

maybe like this

if((Alt(Sum({<Score={">0"}>}Score)/Count({<Score={">0"}>}[Record ID])))>0,

Alt(Sum({<Score={">0"}>}Score)/Count({<Score={">0"}>}[Record ID])/3,0))

View solution in original post

10 Replies
Highlighted
arulsettu
Honored Contributor III

Re: Removing N/A's from a Table

can you share expression inside the variable

hammermill21
Contributor III

Re: Removing N/A's from a Table

Of course!

Alt(Sum(Score)/Count({<Score={">0"}>}[Record ID])/3,0)

arulsettu
Honored Contributor III

Re: Removing N/A's from a Table

N/A is a value from Score? can you share a sample app

hammermill21
Contributor III

Re: Removing N/A's from a Table

It's actually "0" but in the script, I have this:

LOAD RecNo() as ID,

Replace([Facility Temp],'UMHC ','') as Facility,

       If([Score Temp]=0,'N/A',[Score Temp]) as Score,

I just need to remove those N/A for this one table

hammermill21
Contributor III

Re: Removing N/A's from a Table

I attached the sample to the original post above.

arulsettu
Honored Contributor III

Re: Removing N/A's from a Table

maybe like this

if((Alt(Sum({<Score={">0"}>}Score)/Count({<Score={">0"}>}[Record ID])))>0,

Alt(Sum({<Score={">0"}>}Score)/Count({<Score={">0"}>}[Record ID])/3,0))

View solution in original post

hammermill21
Contributor III

Re: Removing N/A's from a Table

I tried something like that and it doesn't seem to work, it throws all the numbers off for some reason.

Partner
Partner

Re: Removing N/A's from a Table

what you get with the below expression:

=Alt( Sum( {< Score = {">0"} >} Score ) / Count( {< Score = {">0"} >} [Record ID] ) /3, 0 )

?

hammermill21
Contributor III

Re: Removing N/A's from a Table

I think I got it, with what you sent me. Thank you!