Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

spikenaylor1
New Contributor III

Expression issues for working out how many batches are correct with other criteria included

We have a Batch that when completed contains 16 documents 

of that 16 documents, 14 have to be right first time.

I need an expression to say

count how many Batch_Numbers where count(doc_name)=16 and where sum(RFT) = >14

just struggling to work out the correct layout of this type of expression

 

many thanks for any help

 

 

 

6 Replies
Partner
Partner

Re: Expression issues for working out how many batches are correct with other criteria included

try with set analysis e.g.
count({<doc_name={"sum(RFT) >=14"}>}doc_name)
spikenaylor1
New Contributor III

Re: Expression issues for working out how many batches are correct with other criteria included

count({<doc_name={"sum(RFT) >=14"}>}doc_name)  just gives me 0

I can see what it is trying to do, but the formula just not working

 

Regards

 

Partner
Partner

Re: Expression issues for working out how many batches are correct with other criteria included

share some sample data/ app. will check.
spikenaylor1
New Contributor III

Re: Expression issues for working out how many batches are correct with other criteria included

here is eventually some sample data

I need the expression to be in a text box, so I can display the numbers and then percentages etc

 

thanks for any help with this

once I get one expression working, I can move on.

Partner
Partner

Re: Expression issues for working out how many batches are correct with other criteria included

Almost there. you need to match set analysis row with a key/identifier dimension you need from the table.

=count(DISTINCT {<Batch_Number={"=sum(RFT) >=14"}>}Batch_Number)

see attached copy with the text box

 

spikenaylor1
New Contributor III

Re: Expression issues for working out how many batches are correct with other criteria included

Yep that works, missed the distinct, brain to focussed on the actual sum formula.

 

Not fully got these set analysis cracked yet, how would I now incorporate:

 

count Batches where RFT is >=14  and sum(Downtime)=0 and sum(DeviationValue) =0

 

so in the example in previous post

I have 3 batches

batch AB1236  is NOT RFT

Our expression result is 2

If the batch AB1236 also had a downtime entry, We would still have 2 Batches correct

 

If batch AB1234 had a downtime entry then we now have only 1 batch correct

Our expression result would now be 1

If Batch AB1234 also had Deviation entry, we would still have only 1 Batch Correct

 

etc

 =count(DISTINCT {<Batch_Number={"=sum(RFT) >=14","=sum(Downtime) =0"}>}Batch_Number) - expression correct but does not work.

Tried other combinations

Tried 

=count(DISTINCT {<Batch_Number={"=sum(RFT) >=14"}>}Batch_Number) - count(DISTINCT {<Batch_Number={"=sum(Downtime) =0"}>}Batch_Number)

 

Many thanks for any help