Count If with a Formula

[#1]

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. 

[JonnyPoole]

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

[agomes1971]

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

Kind regards

André Gomes

[#4]

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)

[#5]

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.

[#6]

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

Thanks.

Mark.

[#7]

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.

[#8]

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.

[JonnyPoole · Accepted solution]

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

[#10]

Perfect!  Exactly what I needed.  Thank you!!

Mark.