topic Re: Count If less than/equal to issue in QlikView

Count If less than/equal to issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I always struggle with a "countif" setup in Qlikview -- it's one of the few things that Excel or Numbers are far better at.

I am trying to count cases if the calculated variance is <= 0.

I created a variable "StartVariance" (one date/time subtracted from another date/time) -- and on my straight table, it shows the expected results.

Ultimately, I need to calculate the percentage of cases that have a variance of 0 or less than 1 (meaning we started on time or early). But I need the count of those cases first.

I searched and found this option:

count({StartVariance=["<=0"]>} DISTINCT CASE_COUNT)

It gives me the result "1" -- it should tell me 46

If I take out the DISTINCT, it just gives me the total number of cases.

I've attached a file with the data pertinent to this expression.

Ideas? Solutions?

Thanks! Chris

Re: Count If less than/equal to issue

sunny_talwar — Thu, 30 Nov 2017 21:10:21 GMT

May be this

Count({<StartVariance = {"<=0"}>} CASE_COUNT)

Re: Count If less than/equal to issue

Anonymous — Thu, 30 Nov 2017 21:26:49 GMT

no, changing the type of brackets doesn't make a difference.

Re: Count If less than/equal to issue

gballester — Thu, 30 Nov 2017 21:33:24 GMT

I attached an example of how to perform the calculations

Re: Count If less than/equal to issue

Anonymous — Thu, 30 Nov 2017 23:43:13 GMT

I'm not sure exactly how you did this, as I can't select the "frequency" if I'm using an expression variable (instead of a pre-defined variable name). And it doesn't really answer my question, unfortunately.

Re: Count If less than/equal to issue

gballester — Fri, 01 Dec 2017 00:22:38 GMT

= count({<Variance={ "<=1"}>} Variance)

= count({<Variance={ "<=1"}>} distinct Variance)

Re: Count If less than/equal to issue

Anonymous — Fri, 01 Dec 2017 05:44:36 GMT

count(if(Variance<=0,Variance)) or count({<Variance={"<=0"}>} Variance)