How to count unique values limited by another column

[Jacek Bartlewicz]

Hi,

I am relatively new to Spotifire and would appreciate support from some more advanced users ;)

I would like write an expression to count the number ofunique subjects in a study with a specific value. For example, from a table below:

 

 

 

Patient

Cohort

Measurement

 

 

101

A

10

 

 

101

A

12

 

 

201

A

10

 

 

201

A

11

 

 

201

A

13

 

 

301

B

10

 

 

401

B

11

 

 

501

B

10

 

 

501

B

11

 

 

 

I would like to Count the number of Patients with Cohort A (so, in the above example, the result would be 2). I tried using the expression

UniqueCount([Patient] when [Cohort] in ("1"))

but the syntax does not seem to be correct. May I kindly ask what the correct expression would be here

[Matthias Garbs]

Try

UniqueCount(case when [Cohort] in ("1") then [Patient] else Null End)

In this way you get first all the patients that fulfill the requirement and then you have a uinque count on those patients.