Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mahnoor1279
Contributor III
Contributor III

How to remove if having negative value in measure

I'm using below expression but it gives incorrect values  with -1 , can anyone please help me out

IF((
COUNT({<[REG_CHANNEL]={'WEB','APP'}>} distinct REG_SNO)
-
COUNT({<[RESULT_STATUS]-={'NULL'},[REQ_TYPE]={'REQ1'}>+
<[ACCT_REJECT]={'NULL'},VERISYS_RESULT={'NULL'},[RESULT_STATUS]={'NULL'},[REQ_TYPE]={'REQ1'},MATCH_UNMATCH={'FALSE'},[FINAL_SUBMIT] ={'Y'}>}
distinct REG_SNO)


) <0 ,0,

COUNT({<[FLAG]={'REG'},[REG_CHANNEL]={'WEB','APP'}>} distinct REG_SNO)
-
COUNT({<[RESULT_STATUS]-={'NULL'},[REQ_TYPE]={'REQ1'}>+
<[ACCT_REJECT]={'NULL'},VERISYS_RESULT={'NULL'},[RESULT_STATUS]={'NULL'},[REQ_TYPE]={'REQ1'},MATCH_UNMATCH={'FALSE'},[FINAL_SUBMIT] ={'Y'}>}
distinct REG_SNO)

)

mahnoor1279_0-1690276063279.png

 

Labels (4)
2 Replies
Or
MVP
MVP

There's not really any way to tell what might cause this formula to return "wrong" values - we don't know what the underlying data is or what the formula is supposed to do.

Generally, if you want to hide rows with a specific result, you should hide zero/null values, and then ensure that the formula returns zero or null for the rows you want to hide. This can be done using If() statements, using e.g. RangeMax(0,YourFormulaHere) to replace negatives with zero, or by other means.

Alternatively, you can use Dimension Limitations to limit the dimension to measure values that are >=0 (with or without an Others bucket).

Mark_Little
Luminary
Luminary

Your set analysis is different in your if statement. 

You second set analysis includes [FLAG]={'REG'}, must be a positive value to then move to else which then returns the negative