Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart over time period's where startdtime < period and endtime > period

Given is a table with the following values:

NameStarttimeEndtime
A8:0010:00
B7:009:00
...

I want to show a barchart with 48 bars where every bar is a time period of an half hour (7:00, 7:30, 8:00, 8:30)

Each Bar should count the count of entries of the given table where the starttime is before the period and the endtime is after the period. The resultset should be:

PeriodNumber of entries
7:001
7:301
8:002
8:302
9:002
9:301
10:001

My thoughts were to create a virtual table for the The Period's and connect them to the Data, but I have no idea how to connect the both sets, cause there is no unique foreign key for that it's kind of a "between-join"

Do you have any idea's to solve that?

1 Solution

Accepted Solutions
Not applicable
Author

Temp:

Load

  RowNo() as Row,

  mod(RowNo(),60) as Minutes,

  mod(div(RowNo(),60),(24)) as Hours

AutoGenerate (24*60);

Temp2:

Load

  Hours,

  Minutes,

  Hours&':'&Minutes as Time,

    Hours&':'&if(Minutes<30,00,30)&' to '&Hours&':'&if(Minutes<30,29,59) as Bucket

//   Hours&':'&Class(Minutes,30) as Bucket2

Resident Temp;

DROP Table Temp;

Try to Map this

View solution in original post

5 Replies
Not applicable
Author

Hi ,

Yes you can create a virtual period table and connect it to the data using INTERVAL MATCH. Check the forum for examples.

Not applicable
Author

When I have an entry C with Starttime = 8:10 and Endime=8:20 it will not be connected with 8:00 but in my case it has to be counted, because it is between 8:00 and 8:30

Not applicable
Author

Instead of creating a virtual table with period of 30 minustes, you can create a virtual table for every minute using autogenerate and the do the interval match.

Not applicable
Author

And how do I ensure that the chart later shows a bar for each 30-minutes step?

Not applicable
Author

Temp:

Load

  RowNo() as Row,

  mod(RowNo(),60) as Minutes,

  mod(div(RowNo(),60),(24)) as Hours

AutoGenerate (24*60);

Temp2:

Load

  Hours,

  Minutes,

  Hours&':'&Minutes as Time,

    Hours&':'&if(Minutes<30,00,30)&' to '&Hours&':'&if(Minutes<30,29,59) as Bucket

//   Hours&':'&Class(Minutes,30) as Bucket2

Resident Temp;

DROP Table Temp;

Try to Map this