Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hammermill21
Creator III
Creator 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
Master III
Master III

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
arulsettu
Master III
Master III

can you share expression inside the variable

hammermill21
Creator III
Creator III
Author

Of course!

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

arulsettu
Master III
Master III

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

hammermill21
Creator III
Creator III
Author

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
Creator III
Creator III
Author

I attached the sample to the original post above.

arulsettu
Master III
Master III

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))

hammermill21
Creator III
Creator III
Author

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

agigliotti
Partner - Champion
Partner - Champion

what you get with the below expression:

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

?

hammermill21
Creator III
Creator III
Author

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