Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How can I count something under certain conditions?

Dear All,

I would like to count how many technicians worked a day
Sick technicians are to be deducted from this figure.

So my formula: Count(Distinct(TECHNIKERNAME))

So far it will calculated correctly.

But how can I deduct the number of technicans where the Job result is "Sick"?

Labels (4)
4 Replies
Highlighted
Partner
Partner

Re: How can I count something under certain conditions?

Hello Bianca,

 

 You could use:

Count(Distinct(TECHNIKERNAME)) - Count({$<[Job result] = {'Sick'}>}Distinct(TECHNIKERNAME))

 

Greetings,

 

Tim Wensink

Highlighted
Creator II
Creator II

Re: How can I count something under certain conditions?

Count({<[Job result] -= {Sick}>} Distinct [TECHNIKERNAME])
Highlighted
Partner
Partner

Re: How can I count something under certain conditions?

Great option too! The difference with my answer is, however, that in this case all TECHNIKERNAMEs will be counted that have a value other than Sick in the field Job result. If there are TECHNIKERNAMEs with a null value in this field, they will not be counted. So I guess it depends on the data available what the best solution is.

Highlighted
Creator II
Creator II

Re: How can I count something under certain conditions?

@Tim_Wensink: interesting observation although in that case I would use another expression.

 

Count({<[TECHNIKERNAME] *= E({<[Job result] = {Sick}>})>} Distinct [TECHNIKERNAME])

 

Assuming that we use your measure in a straight table, we have the following problems to solve...

a) Records whose value is equal to "Sick" in the [Job result] field will appear with a value of 0 (zero).
Solution: uncheck the checkbox Include zero values.

TimWensinkMeasure_1.PNG

TimWensinkMeasure_2.PNG

 

b) When I select on the [Job result] field I get wrong results.
Solution: add intersection operator (*).

TimWensinkMeasure_3.PNG

TimWensinkMeasure_4.PNG