Taking count in hourly basis

[QlikNewBie30]

Hi Experts,

I have an issue with my QlikSense dashboard.

I have a table with Cust ID, X_date and Y_date.

I want to create a bar chart with hours like 8-9, 9-10,10-11 and i want to count the number of customers who came before or start of interval and left at or after end of interval.

For example

Cust_ID          X_date                                        Y_date                             

            1                  5/1/19 7:00                    5/1/19 10:00
            2                  5/1/19 8:00                    5/1/19 10:30
            3                  5/1/19 9:00                    5/1/19 11:00
            4                  5/1/19 10:00                  5/1/19 11:30
            5                  5/1/19 11:00                  5/1/19 12:00
            6                  5/1/19 12:00                  5/1/19 13:30
            7                  5/1/19 13:00                  5/1/19 15:00
            8                  5/1/19 14:00                  5/1/19 16:30
            9                  5/1/19 15:00                  5/1/19 18:00

I want below to show on my chart. The count basically means, how many customers came before that hour and left at or after next hour.

Hour on chart                count

12:00:00 AM                 0
1:00:00 AM                   0
2:00:00 AM                   0
3:00:00 AM                   0
4:00:00 AM                   0
5:00:00 AM                   0
6:00:00 AM                   0
7:00:00 AM                   1
8:00:00 AM                   2
9:00:00 AM                   3

The count above is showing 3, that means all the people who came before or at 9 and left at or after 10.

I am very new qliksense and I am not able to figure out how to get this done.

Best Regards,

[JordyWegman]

Hi,

You need a interval match for this.

HourOnChart:
Load * Inline [
Hours 
12:00:00 AM                 
1:00:00 AM                   
2:00:00 AM                   
3:00:00 AM                   
4:00:00 AM                   
5:00:00 AM                   
6:00:00 AM                   
7:00:00 AM                   
8:00:00 AM                   
9:00:00 AM                  
];

[YourData]:
Load * Inline [
Cust_ID,          X_date,                                        Y_date                             

            1,                  5/1/19 7:00,  5/1/19 10:00
            2,                  5/1/19 8:00,  5/1/19 10:30
            3,                  5/1/19 9:00,  5/1/19 11:00
            4,                  5/1/19 10:00, 5/1/19 11:30
            5,                  5/1/19 11:00, 5/1/19 12:00
            6,                  5/1/19 12:00, 5/1/19 13:30
            7,                  5/1/19 13:00, 5/1/19 15:00
            8,                  5/1/19 14:00, 5/1/19 16:30
            9,                  5/1/19 15:00, 5/1/19 18:00
];

Inner Join IntervalMatch (Hours)
Load
X_Date,
Y_Date
Resident [YourData];

The only thing that is not working now is the date format you use. Align them so you have both the same timestamps. Otherwise this won't work. But you can use the setup I've made.

Jordy

Climber

[QlikNewBie30]

Thank you Jordi for the quick reply.

I am trying to get the count of customers (for example) who came before or at 9 but left after 10.

By interval match I understand would count the number who came between 9-10.

Hope I am making sense here.

Is there a way to do this?

[JordyWegman]

To be more precise, you want to know during what hours how many customers are there at a exact moment.

For example:

1                  5/1/19 7:00                    5/1/19 08:30
2                  5/1/19 8:00                    5/1/19 08:30
3                  5/1/19 9:00                    5/1/19 11:00

07:00 -> 1
08:00 -> 2
09:00 -> 1

So you want to know how many people are their during 7, 8 and 9. For 7 that is just 1, for 8 that is 2 because of 1 and 2 and at 9 there is only one. 

Is this correct?

Jordy

Climber 

[QlikNewBie30]

Hi Jordy,

Thank you for your patience.

at 9, the count will be 3 because , 3 people came before or at 9 but left at or after 10.

Similarly at 8, the count will be 2, because 2 people came before or at 8 and left at or after 9.

ID         X-date               Y-date

  1         5/1/19 7:00    5/1/19 10:00                             So at 8 in x-axis, the count , we will count both 1 and 2 ID because they
    2       5/1/19 8:00    5/1/19 10:30                              came at or before 8 and left at or after 9 
   3        5/1/19 9:00    5/1/19 11:00                             Same way at 9, I will consider 1,2,3 IDs because they all came before or
     4      5/1/19 10:00  5/1/19 11:30                             at 9 but left at or after 10. Again for 10, the count will be 2 because, 4 
    5       5/1/19 11:00  5/1/19 12:00                             people came before 10 or  at 10 but only 2 out of 4 left at or after 11
    6      5/1/19 12:00  5/1/19 13:30
    7     5/1/19 13:00  5/1/19 15:00                              8   count->2
      8     5/1/19 14:00  5/1/19 16:30                            9   count->3

                                                                                               10 count->2

Hope its clearer now  .

Thank you in advance                                                 

[JordyWegman]

Hi,

It's kind of hard to know how it's set up, but you always take current time + 1 hour as the maximum time?

So you need a formula that is counting the amount of X dates that are before a certain time and subtract the Y dates that are lower that the X date + 1 hour. 

Could you manage that?

Jordy
Climber