Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Buddika
Contributor
Contributor

Number of Patients on a Day

I want to count the number of patients on a day.

Patients can be discharged on any given day (see sample table).

I have used following set function to calculate number of patients on a day, Rangesum(Above(Aggr(Count(DC),[date in]),0,RowNo())) –

Rangesum(Above(Aggr(Count(DC),[date out]),1,RowNo()))

but failed to get the correct numbers. 

Patient No.

Date in

Date out

1

2021-08-18 00:00:00

2021-08-18 00:00:00

2

2021-08-18 00:00:00

2021-08-22 00:00:00

3

2021-08-18 00:00:00

2021-08-25 00:00:00

4

2021-08-18 00:00:00

2021-08-18 00:00:00

5

2021-08-18 00:00:00

2021-08-29 00:00:00

6

2021-08-18 00:00:00

2021-08-18 00:00:00

7

2021-08-18 00:00:00

2021-08-29 00:00:00

8

2021-08-18 00:00:00

2021-08-18 00:00:00

9

2021-08-18 00:00:00

2021-08-18 00:00:00

10

2021-08-18 00:00:00

2021-08-19 00:00:00

11

2021-08-18 00:00:00

2021-08-19 00:00:00

12

2021-08-18 00:00:00

2021-08-22 00:00:00

13

2021-08-18 00:00:00

2021-08-18 00:00:00

14

2021-08-18 00:00:00

2021-08-18 00:00:00

15

2021-08-19 00:00:00

2021-08-19 00:00:00

16

2021-08-19 00:00:00

2021-08-22 00:00:00

17

2021-08-19 00:00:00

2021-08-19 00:00:00

18

2021-08-19 00:00:00

2021-08-19 00:00:00

19

2021-08-20 00:00:00

2021-08-30 00:00:00

20

2021-08-20 00:00:00

2021-08-22 00:00:00

1 Solution

Accepted Solutions
Saravanan_Desingh

Try like this,

SET TimestampFormat='M/D/YYYY h:mm:ss';

tab1:
LOAD * INLINE [
    Patient No., Date in, Date out
    1, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    2, 8/18/2021 00:00:00, 8/22/2021 00:00:00
    3, 8/18/2021 00:00:00, 8/25/2021 00:00:00
    4, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    5, 8/18/2021 00:00:00, 8/29/2021 00:00:00
    6, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    7, 8/18/2021 00:00:00, 8/29/2021 00:00:00
    8, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    9, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    10, 8/18/2021 00:00:00, 8/19/2021 00:00:00
    11, 8/18/2021 00:00:00, 8/19/2021 00:00:00
    12, 8/18/2021 00:00:00, 8/22/2021 00:00:00
    13, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    14, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    15, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    16, 8/19/2021 00:00:00, 8/22/2021 00:00:00
    17, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    18, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    19, 8/20/2021 00:00:00, 8/30/2021 00:00:00
    20, 8/20/2021 00:00:00, 8/22/2021 00:00:00
];

Left Join(tab1)
LOAD *,   Date(Floor([Date in])+IterNo()-1) As Dt
Resident tab1
While Date(Date(Floor([Date in]))+IterNo()-1) <= Date([Date out])
;

commQV01.PNG

View solution in original post

6 Replies
sunny_talwar

I suggest using Interval Match for this. You will basically interval match Date In and Date Out with your master calendar. and then using Date as your dimension, you can just do Count(DISTINCT [Patient No.]) to get the result you are looking for.

Buddika
Contributor
Contributor
Author

Thanks, Sunny.

sunny_talwar

Your welcome. I hope you were able to figure it out

Saravanan_Desingh

Try like this,

SET TimestampFormat='M/D/YYYY h:mm:ss';

tab1:
LOAD * INLINE [
    Patient No., Date in, Date out
    1, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    2, 8/18/2021 00:00:00, 8/22/2021 00:00:00
    3, 8/18/2021 00:00:00, 8/25/2021 00:00:00
    4, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    5, 8/18/2021 00:00:00, 8/29/2021 00:00:00
    6, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    7, 8/18/2021 00:00:00, 8/29/2021 00:00:00
    8, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    9, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    10, 8/18/2021 00:00:00, 8/19/2021 00:00:00
    11, 8/18/2021 00:00:00, 8/19/2021 00:00:00
    12, 8/18/2021 00:00:00, 8/22/2021 00:00:00
    13, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    14, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    15, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    16, 8/19/2021 00:00:00, 8/22/2021 00:00:00
    17, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    18, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    19, 8/20/2021 00:00:00, 8/30/2021 00:00:00
    20, 8/20/2021 00:00:00, 8/22/2021 00:00:00
];

Left Join(tab1)
LOAD *,   Date(Floor([Date in])+IterNo()-1) As Dt
Resident tab1
While Date(Date(Floor([Date in]))+IterNo()-1) <= Date([Date out])
;

commQV01.PNG

Buddika
Contributor
Contributor
Author

Thanks, Saravanan.

That works very well.

 

Saravanan_Desingh

If u think your query is answered, please mark the right answer and close the thread.