Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Introducing a new Enhanced File Management feature in Qlik Cloud! GET THE DETAILS!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Qliksense_77
Creator
Creator
‎2022-07-06 04:53 AM

Problem with sum aggr count

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
Labels (1)
Labels
938 Views
0 Likes
1 Solution

Accepted Solutions
Or
MVP
MVP
‎2022-07-06 05:26 AM

In response to Qliksense_77

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.

View solution in original post

930 Views
0 Likes
6 Replies
Or
MVP
MVP
‎2022-07-06 04:57 AM

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)?

 

936 Views
0 Likes
Qliksense_77
Creator
Creator
‎2022-07-06 05:17 AM
Author

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
932 Views
0 Likes
Or
MVP
MVP
‎2022-07-06 05:26 AM

In response to Qliksense_77

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.

931 Views
0 Likes
Qliksense_77
Creator
Creator
‎2022-07-06 05:40 AM
Author

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.

 

926 Views
0 Likes
Or
MVP
MVP
‎2022-07-06 05:42 AM

In response to Qliksense_77

Why would it be filtered? It has a datetime of 01-03-21 20

Or_0-1657100499133.png

Perhaps the issue is with your underlying data structure?

 

920 Views
0 Likes
Qliksense_77
Creator
Creator
‎2022-07-06 05:55 AM
Author

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.

909 Views