topic Re: Count If with a Formula in QlikView

Count If with a Formula

Mon, 28 Jul 2014 16:16:27 GMT

I need to do calculations on the last 15 runs of some test data. I can determine the number of tests Count(Distinct Test_Name), I can determine the percentage that pass (I have a PassCount and TestCount value in the data). However, what I now am being asked to do is count the number of tests (count Test_Name) WHERE (PassCount / TestCount) > .93.

I have tried several different items all of which are flagged as invalid formula:

Sum(if((PassCount / TestCount) >= .93), 1, 0)

Count(if((PassCount / TestCount) >= .93), Distinct Test_Name)

I don't know where to go from here. The individual tests may or may not have 15 tests. Each test and its result are on a different row in the data.

Any thoughts?

By the way, using v11 SP5/6 (depending on which user will end up using the file) and I am relatively new to QV having been using it about six weeks.

Thank you.

Re: Count If with a Formula

JonnyPoole — Mon, 28 Jul 2014 16:48:31 GMT

A sample data file would help. Can you attach/share one ?

Re: Count If with a Formula

agomes1971 — Mon, 28 Jul 2014 16:53:57 GMT

Please see this post: Count IF in QlikView (Number of Days)

Kind regards

André Gomes

Re: Count If with a Formula

Mon, 28 Jul 2014 17:05:49 GMT

You can also set a new flag of tests with over .93 in the load statement and sum this flag up in your charts:

LOAD

...

If( (PassCount/TestCount) >= .93, 1) as TestFlag

...

In the UI, you can use Sum(TestFlag)

Re: Count If with a Formula

Mon, 28 Jul 2014 17:12:43 GMT

Document with some sample data is attached. I have a couple of pivots/tables showing data and some statistics boxes at the top showing overall info. What I can't figure out is populating the rightmost statistics box (highlighted).

Thank you.

Re: Count If with a Formula

Mon, 28 Jul 2014 17:17:34 GMT

Attached file. Sorry, responded to my post, not this one but it is now available.

Thanks.

Mark.

Re: Count If with a Formula

Mon, 28 Jul 2014 17:19:40 GMT

Seems to be a similar type issue but not as complex. I do not see how to extrapolate that example into my scenario. Can the calculation have additional limits applied to it? In my case, Source? I need to show by my two sources as well as a total (i.e., three different expression values).

Thanks.

Mark.

Re: Count If with a Formula

Mon, 28 Jul 2014 17:22:00 GMT

That seems like it could work but I am loading the data from SQL and am not sure how I can tell it to Sum(PassCount) / Sum(TestCount) by Test_Name. Perhaps I need to improve my SQL...

Thanks.

Mark.

Re: Re: Count If with a Formula

JonnyPoole — Mon, 28 Jul 2014 17:29:00 GMT

ok . looks like you need an advanced aggregation to figure out how many tests had a greater than 93% pass rate over their individual runs.

=sum(aggr(if(sum(PassCount)/sum(TestCount) > .93,1,0),Test_Name))

also attached is an example QVW

Re: Count If with a Formula

Mon, 28 Jul 2014 17:41:05 GMT

Perfect! Exactly what I needed. Thank you!!

Mark.