How to do a WHERE not Equal Zero in an Expression

[paul_ripley]

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

[Gysbert_Wassenaar]

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

[paul_ripley]

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.

[paul_ripley]

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

[wilsonwebb]

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]

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]

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