Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data with number of patients in beds per hour.
I want to do be able to do a count of how many patients where there at a specific hour and day.
I seems that I get the correct count. But it is the wrong rows that get counted.
Below is example with filter on the datetime: 01-03-21 20:00
Why is it the first row of id 12570334 that gets counted?
It is the 2. row for id 12570334 that shoud get counted. And the first row should not be shown at all.
I am using this expression: sum(aggr( count(distinct DateTime), ID))
Can anybody help?
ID | DateTime | Date_start | Time_Start | Date_end | Time_end | sum(aggr( count(distinct DateTime), ID)) |
Totals | 20 | |||||
12570334 | 01-03-21 20:00 | 05-11-2020 | 14 | 05-11-2020 | 23:50:00 | 1 |
12570334 | 01-03-21 20:00 | 03-02-2021 | 12 | 05-03-2021 | 10:10:00 | 0 |
21161371 | 01-03-21 20:00 | 01-03-2021 | 08 | 02-03-2021 | 12:30:00 | 0 |
21161371 | 01-03-21 20:00 | 03-03-2021 | 10 | 08-03-2021 | 11:30:00 | 1 |
21167460 | 01-03-21 20:00 | 17-02-2021 | 09 | 08-03-2021 | 13:30:00 | 1 |
21218243 | 01-03-21 20:00 | 15-02-2021 | 10 | 03-03-2021 | 17:02:00 | 1 |
21219252 | 01-03-21 20:00 | 15-02-2021 | 08 | 05-03-2021 | 14:30:00 | 1 |
21224589 | 01-03-21 20:00 | 01-03-2021 | 08 | 02-03-2021 | 13:35:00 | 1 |
21224589 | 01-03-21 20:00 | 03-03-2021 | 10 | 05-03-2021 | 13:15:00 | 0 |
21225495 | 01-03-21 20:00 | 26-02-2021 | 08 | 04-03-2021 | 18:30:00 | 1 |
21227600 | 01-03-21 20:00 | 17-02-2021 | 10 | 17-02-2021 | 20:30:00 | 1 |
21227600 | 01-03-21 20:00 | 18-02-2021 | 10 | 08-03-2021 | 14:30:00 | 0 |
21228969 | 01-03-21 20:00 | 24-02-2021 | 08 | 02-03-2021 | 18:20:00 | 1 |
21228969 | 01-03-21 20:00 | 29-03-2021 | 14 | 30-03-2021 | 03:50:00 | 0 |
21228969 | 01-03-21 20:00 | 31-03-2021 | 00 | 09-04-2021 | 09:51:00 | 0 |
21233685 | 01-03-21 20:00 | 26-02-2021 | 08 | 05-03-2021 | 13:30:00 | 1 |
21236832 | 01-03-21 20:00 | 20-02-2021 | 09 | 07-05-2021 | 12:15:00 | 1 |
21238107 | 01-03-21 20:00 | 01-03-2021 | 08 | 03-03-2021 | 00:00:00 | 0 |
21238107 | 01-03-21 20:00 | 03-03-2021 | 16 | 07-03-2021 | 20:00:00 | 1 |
21244891 | 01-03-21 20:00 | 23-02-2021 | 18 | 12-03-2021 | 15:30:00 | 1 |
21252732 | 01-03-21 20:00 | 24-02-2021 | 19 | 17-03-2021 | 20:00:00 | 1 |
21256481 | 01-03-21 20:00 | 26-02-2021 | 14 | 26-03-2021 | 11:00:00 | 1 |
21256813 | 01-03-21 20:00 | 25-02-2021 | 19 | 25-02-2021 | 19:11:00 | 0 |
21256813 | 01-03-21 20:00 | 25-02-2021 | 21 | 29-03-2021 | 10:25:00 | 1 |
21257020 | 01-03-21 20:00 | 01-03-2021 | 16 | 02-03-2021 | 14:00:00 | 0 |
21257020 | 01-03-21 20:00 | 03-03-2021 | 11 | 06-03-2021 | 15:03:00 | 1 |
21258853 | 01-03-21 20:00 | 28-02-2021 | 10 | 28-02-2021 | 10:55:00 | 0 |
21258853 | 01-03-21 20:00 | 01-03-2021 | 11 | 05-03-2021 | 15:30:00 | 1 |
21262714 | 01-03-21 20:00 | 01-03-2021 | 15 | 01-03-2021 | 15:55:00 | 0 |
21262714 | 01-03-21 20:00 | 01-03-2021 | 17 | 05-03-2021 | 19:15:00 | 1 |
21262949 | 01-03-21 20:00 | 01-03-2021 | 14 | 01-03-2021 | 14:00:00 | 1 |
21262949 | 01-03-21 20:00 | 01-03-2021 | 18 | 02-03-2021 | 18:04:00 | 0 |
I'm confused by your requirement.
You can't have Date_Start as a dimension in your table without showing both rows for ID 12570334, because it has two different values in this field. Either you want to show a detailed table (which means you'll see multiple rows per ID) or you want a summary table (which means you don't get one line per each of these fields). It has to be one or the other.
This looks to be an issue where your aggregation level and your dimensions mismatch. If you remove the non-ID dimensions, this should work correctly, but if e.g. Date_start is a dimension, then that's going to cause both rows to appear (unless you have Hide 0 and the only measure is the last one, perhaps).
If ID represents a patient, shouldn't you just be using Count(distinct ID)?
Thanks for the input @Or
Unfortunately changing to count(distinct ID) does not help.
The not relevant rows are still there - se below. And I need the non-ID dimensions in the table.
ID | DateTime | Date_start | Time_Start | Date_end | Time_end | Count(Distinct ID) |
Totals | 20 | |||||
12570334 | 01-03-2021 20 | 05-11-2020 | 14 | 05-11-2020 | 23 | 1 |
12570334 | 01-03-2021 20 | 03-02-2021 | 12 | 05-03-2021 | 10 | 1 |
21161371 | 01-03-2021 20 | 01-03-2021 | 08 | 02-03-2021 | 12 | 1 |
21161371 | 01-03-2021 20 | 03-03-2021 | 10 | 08-03-2021 | 11 | 1 |
21167460 | 01-03-2021 20 | 17-02-2021 | 09 | 08-03-2021 | 13 | 1 |
21218243 | 01-03-2021 20 | 15-02-2021 | 10 | 03-03-2021 | 17 | 1 |
21219252 | 01-03-2021 20 | 15-02-2021 | 08 | 05-03-2021 | 14 | 1 |
21224589 | 01-03-2021 20 | 01-03-2021 | 08 | 02-03-2021 | 13 | 1 |
21224589 | 01-03-2021 20 | 03-03-2021 | 10 | 05-03-2021 | 13 | 1 |
21225495 | 01-03-2021 20 | 26-02-2021 | 08 | 04-03-2021 | 18 | 1 |
21227600 | 01-03-2021 20 | 17-02-2021 | 10 | 17-02-2021 | 20 | 1 |
21227600 | 01-03-2021 20 | 18-02-2021 | 10 | 08-03-2021 | 14 | 1 |
21228969 | 01-03-2021 20 | 24-02-2021 | 08 | 02-03-2021 | 18 | 1 |
21228969 | 01-03-2021 20 | 29-03-2021 | 14 | 30-03-2021 | 03 | 1 |
21228969 | 01-03-2021 20 | 31-03-2021 | 00 | 09-04-2021 | 09 | 1 |
21233685 | 01-03-2021 20 | 26-02-2021 | 08 | 05-03-2021 | 13 | 1 |
21236832 | 01-03-2021 20 | 20-02-2021 | 09 | 07-05-2021 | 12 | 1 |
21238107 | 01-03-2021 20 | 01-03-2021 | 08 | 03-03-2021 | 00 | 1 |
21238107 | 01-03-2021 20 | 03-03-2021 | 16 | 07-03-2021 | 20 | 1 |
21244891 | 01-03-2021 20 | 23-02-2021 | 18 | 12-03-2021 | 15 | 1 |
21252732 | 01-03-2021 20 | 24-02-2021 | 19 | 17-03-2021 | 20 | 1 |
21256481 | 01-03-2021 20 | 26-02-2021 | 14 | 26-03-2021 | 11 | 1 |
21256813 | 01-03-2021 20 | 25-02-2021 | 19 | 25-02-2021 | 19 | 1 |
21256813 | 01-03-2021 20 | 25-02-2021 | 21 | 29-03-2021 | 10 | 1 |
21257020 | 01-03-2021 20 | 01-03-2021 | 16 | 02-03-2021 | 14 | 1 |
21257020 | 01-03-2021 20 | 03-03-2021 | 11 | 06-03-2021 | 15 | 1 |
21258853 | 01-03-2021 20 | 28-02-2021 | 10 | 28-02-2021 | 10 | 1 |
21258853 | 01-03-2021 20 | 01-03-2021 | 11 | 05-03-2021 | 15 | 1 |
21262714 | 01-03-2021 20 | 01-03-2021 | 15 | 01-03-2021 | 15 | 1 |
21262714 | 01-03-2021 20 | 01-03-2021 | 17 | 05-03-2021 | 19 | 1 |
21262949 | 01-03-2021 20 | 01-03-2021 | 14 | 01-03-2021 | 14 | 1 |
21262949 | 01-03-2021 20 | 01-03-2021 | 18 | 02-03-2021 | 18 | 1 |
I'm confused by your requirement.
You can't have Date_Start as a dimension in your table without showing both rows for ID 12570334, because it has two different values in this field. Either you want to show a detailed table (which means you'll see multiple rows per ID) or you want a summary table (which means you don't get one line per each of these fields). It has to be one or the other.
ok thanks,@Or
I dont really understand why the first row for ID 12570334 is not filtered away, when I filter to datetime: 01-03-21 20:00.
But if its not possible to filter away not relevant stays, then I will just make a summary table.
Why would it be filtered? It has a datetime of 01-03-21 20
Perhaps the issue is with your underlying data structure?
I should be filtered, because the stay should not have a datetime of 01-03-21 20 - the stay is from 05-11-20 to 05-11-20.
I guess you are right, that there is something wrong with the data structure.