Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
ID | Price |
a | $10 |
b | $21 |
c | $45 |
d | $84 |
e | $7 |
f | $48 |
g | $58 |
h | $61 |
i | $23 |
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)
I know this is 5 years old, but did you figure out a solution? I have a similar problem.