Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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!