Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

how do i exclude a 0 value within set analysis

Hi,

My requirement is as below

I have a date column, when I select a date, from that date I need to consider 52 weeks back and fetch the lowest price .I need to exclude the 0 and display the next lowest value.

DT_KEY Price
31/12/2014 671.65
31/12/2013 200
31/12/2013 0

Here if I select the date as 31/12/2014 ,I need to take 52 weeks prior to that and fetch the lowest price.here the lowest price is 0 but I don't want to fetch 0 instead want to fetch the next lowest price i.e 200.

Below I have the statement to fetch values for minimum value ,but the result is fetching as 0.

=if(price<>0,min({<DT_KEY={">=$(=DT_KEY($(vStart)))<=$(=DT_KEY($(vPrevious)))"}>}price))

Please help me to get the right expression to fetch minimum price excluding 0.

4 Replies
jontydkpi
Not applicable

Re: how do i exclude a 0 value within set analysis

Hi

Like this:

=min({<DT_KEY={">=$(=vStart)<=$(=vPrevious)"}, price-={0}>} price))

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: how do i exclude a 0 value within set analysis

Hi Jonathan,

Am not getting the result still.i am getting a blank value.

Tried using the below also.but still unable to fetch the minimum value apart from 0.

=min({<DT_KEY={">=$(=vStart)<=$(=vPrevious)"},price={'*'}-{'0'}>} price)

sasikanth
Not applicable

Re: how do i exclude a 0 value within set analysis

hi try some thing like

min({<DT_KEY={">=$(=vStart)<=$(=vPrevious)"},price={'*'}-{'0'}>}  if( not isnull(price),price)

tobias_klett
Not applicable

Re: how do i exclude a 0 value within set analysis

Hi,

since the price will not be negative just say ">0" like this:

min({<DT_KEY={">=$(=vStart)<=$(=vPrevious)"},price={">0"}>}  if( not isnull(price),price)


Regards

Tobias