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

how do i exclude a 0 value within set analysis

Fri, 20 Feb 2015 05:28:35 GMT

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.

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

jonathandienst — Fri, 20 Feb 2015 05:35:16 GMT

Like this:

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

HTH

Jonathan

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

Fri, 20 Feb 2015 07:04:08 GMT

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)

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

sasikanth — Fri, 20 Feb 2015 10:35:02 GMT

hi try some thing like

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

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

tobias_klett — Fri, 20 Feb 2015 10:41:58 GMT

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