How can you Count the number of patient with more than one AppointmentId in the same day

[MEDFAR]

Hello, I've been trying to build build a widget (NUMBER) where I get the total Count of PatientId  with more than one appointment in the same day... I was able to build a pivot table but I am confused when I try to replicate the calculation in other widget. Here is what I've been trying to do:

(COUNT([PatientId]), COUNT([AppointmentId[), [AppointmentDate]) 

The problem is I can't filter the number of AppointmentId to be greater than 1 because AppointmentId is a String and Sisense doesn't offer the option to filter by value....

Thank you in advance for your help!

Olivier

[harikm007]

Hi @MEDFAR ,

Try CASE statement to check if number of AppointmentId is greater than 1 within multipass aggregation.

The formula would be something like this:

(SUM([PatientId], 
case when (COUNT([AppointmentId]), [AppointmentDate]) > 1 
  then 1 
  else 0 
end)

-Hari

[MEDFAR]

Yes! Amazing, thank you so much it is working perfectly. 

Thank you for your help harikm007, it is very appreciated !

Have a very nice day, 

Olivier D.