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: 
paul_ripley
Creator III
Creator III

How to do a WHERE not Equal Zero in an Expression

Hello

I have an expression

=min(aggr(floor(sum({$<period = {">=$(=$(vPeriodSet_PY)*100+12)<=$(vEndPeriod_CY)"}-{"$(=$(vPeriodSet_PY)*100+13)"}
  
, Datasource = {'Actual'} >} GBP)+50000,100000)/1000000,period))

I want to exclude where the value is zero.

How would I do this?

Many thanks

Paul

9 Replies
Gysbert_Wassenaar

Which value? The result of the min(....) expression? Or of the sum(...) part?
And Qlikview or Qlik Sense?

talk is cheap, supply exceeds demand
paul_ripley
Creator III
Creator III
Author

Hi Gysbert

It's where the min is not zero.

 

Many thanks

Paul

Gysbert_Wassenaar

If you're using that expression in a chart then you can specify that the chart should not show zero values. In Qlik Sense you can find that option in the Add-ons section under Data handling. There you can uncheck the option Include zero values. In Qlikview you can enable the Suppress Zero-Values option on the Presentation tab of the charts properties window.

talk is cheap, supply exceeds demand
paul_ripley
Creator III
Creator III
Author

Hi Gysbert

This is in Qlikview and the expression is being used in a reference line.

 

 

Thiago_Justen_

Could be this:
min({<GBP={">0"}>}aggr(floor(sum({$<period = {">=$(=$(vPeriodSet_PY)*100+12)<=$(vEndPeriod_CY)"}-{"$(=$(vPeriodSet_PY)*100+13)"}
, Datasource = {'Actual'} >} GBP)+50000,100000)/1000000,period))
Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
wilsonwebb
Partner - Contributor III
Partner - Contributor III

Hi Paul,

 

I also do not see the min. Nevertheless, to do a not equal to 0 in set analysis you go
count({<field ={'-=0'} >} value)

what you are looking for is {'-=0'} . Let me know if this helps you somehow with your logic.

paul_ripley
Creator III
Creator III
Author

Hi Thiago

Thanks for trying, but I cant seem to get it to work.

I'll take a bit more time myself

Thanks

Paul

 

paul_ripley
Creator III
Creator III
Author

also thanks Wilsonwebb

You've given me ideas ill see if I can it to work

Thiago_Justen_

You can also create a flag when GBP value is 0. Just like:

Load
Other_Fields,
If (NumSum (GBP)=0,'Yes','No') as Flag_GBP
From yourDB;

Then on the UI you should try this expression:
min({<Flag_GBP={'No'}>}aggr(floor(sum({$<period = {">=$(=$(vPeriodSet_PY)*100+12)<=$(vEndPeriod_CY)"}-{"$(=$(vPeriodSet_PY)*100+13)"}
, Datasource = {'Actual'} ,Flag_GBP={'No'}>} GBP)+50000,100000)/1000000,period))
Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago