Forum Discussion

MEDFAR's avatar
MEDFAR
Cloud Apps
01-06-2023

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

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

Code-first Analytics

2 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    01-08-2023

    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's avatar
    MEDFAR
    Cloud Apps
    01-09-2023

    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.