Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Nested Count-If function

I 'm new to Qlik, and am trying to figure out a relatively complex countif statement.  I'm looking at a list of prices that pertain to unique ID's, and trying to get a count of the prices that fall between the mean +1 standard deviation and the mean - 1 standard deviation.  In Excel, I would do it like this:

=count(price) - countif(price<(average(price)-stdev(price)) - countif(price>(average(price)+stdev(price))

Basically, take the total count - anything that falls outside my range.  Thoughts?

 

IDPrice
a$10
b$21
c$45
d$84
e$7
f$48
g$58
h$61
i$23
1 Reply

Re: Nested Count-If function

Create two variables:

     vUpperLimit: =avg(Price)+stdev(Price)

     vLowerLimit: =avg(Price)-stdev(Price)


And use the variables in a set analysis expression :

     Count({<Price={'>=$(vLowerLimit)<=$(vUpperLimit)'}>}Price)


talk is cheap, supply exceeds demand