topic Re: How to do a WHERE not Equal Zero in an Expression in QlikView

How to do a WHERE not Equal Zero in an Expression

paul_ripley — Fri, 16 Nov 2018 16:33:35 GMT

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

Re: How to do a WHERE not Equal Zero in an Expression

Gysbert_Wassenaar — Fri, 16 Nov 2018 17:12:08 GMT

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

Re: How to do a WHERE not Equal Zero in an Expression

paul_ripley — Mon, 19 Nov 2018 11:37:52 GMT

Hi Gysbert

It's where the min is not zero.

Many thanks

Paul

Re: How to do a WHERE not Equal Zero in an Expression

Gysbert_Wassenaar — Mon, 19 Nov 2018 17:14:55 GMT

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.

Re: How to do a WHERE not Equal Zero in an Expression

paul_ripley — Tue, 20 Nov 2018 09:57:45 GMT

Hi Gysbert

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

Re: How to do a WHERE not Equal Zero in an Expression

Thiago_Justen_ — Tue, 20 Nov 2018 10:00:51 GMT

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

Re: How to do a WHERE not Equal Zero in an Expression

wilsonwebb — Tue, 20 Nov 2018 10:04:30 GMT

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.

Re: How to do a WHERE not Equal Zero in an Expression

paul_ripley — Tue, 20 Nov 2018 10:19:47 GMT

Hi Thiago

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

I'll take a bit more time myself

Thanks

Paul

Re: How to do a WHERE not Equal Zero in an Expression

paul_ripley — Tue, 20 Nov 2018 10:21:39 GMT

also thanks Wilsonwebb

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

Re: How to do a WHERE not Equal Zero in an Expression

Thiago_Justen_ — Tue, 20 Nov 2018 11:34:25 GMT

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