How can I count something under certain conditions?

[BiancaW]

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"?

[Tim_Wensink]

Hello Bianca,

 You could use:

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

Greetings,

Tim Wensink

[JGMDataAnalysis]

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

[Tim_Wensink]

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.

[JGMDataAnalysis]

@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.

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