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: 
qw_johan
Creator
Creator

Problem displaying scheduled hours

Hi,

I have a problem displaying scheduled hours.

My data looks like image below (also see attached QV file)...id, date_from, time_from, date_to, time_to

MyQV.jpg

I want to use this information and display, in a chart (and table), how people schedule their hours, hour by hour.

So far I have been able to show when people start/finish ( see below), hour by hour...

But I really want to see when people are working (not just when they start or finish), hour by hour....

Like, how many people are working at a specifik time, hour by hour.

Anybody that knows how I can do that?

Any help is appreciated.

Also, see attached QV.

Thanks,

Johan

1 Solution

Accepted Solutions
Not applicable

Hi Johan

I had a similar issue to count how many stores are open at any time

Look at this qhare qv.

http://community.qlik.com/qlikviews/1066

Hope it could solve your isse.

JJ

View solution in original post

17 Replies
Not applicable

Hi Johan

I had a similar issue to count how many stores are open at any time

Look at this qhare qv.

http://community.qlik.com/qlikviews/1066

Hope it could solve your isse.

JJ

qw_johan
Creator
Creator
Author

Hi JJ!

Your reply (QV) was very helpful. I successfully "copied" your approach and was able to

get the chart I was looking for. Thank you very much.

MyQV.jpg

****** CODE ******

[Scheduled time]:

LOAD ID,

     date_from,

     time_from,

     date_to,

     time_to,

     1 as number_of_records,

     WeekDay(date#(date_from, 'YYYYMMDD')) as work_start_day,

     time(MakeTime(left(time_from,2))) as KEY_HOUR_START,

     time(MakeTime(left(time_to,2))) as KEY_HOUR_END

FROM

test_data.qvo(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

TIME_RANGE:

Load time(maketime(rowno())) as HOUR

Autogenerate(24) ;

In chart...

= sum (  aggr( Max( if( HOUR >= KEY_HOUR_START and  HOUR  < KEY_HOUR_END ,1 , 0) )  , ID, HOUR) )

and HOUR as dimension.

****** CODE ******

Just have a few more questions that maybe you can help me with.

Can I make this display every half-hour?

Why doesn't the chart start at 00:00:00 and go all the way until 23:00:00....I know I have times there....?

Thanks,

Johan

Not applicable

Nice !

To work with half-hour,  you can generate a new field in the script  with 48 values 0, 0.5,1,..., 24 :

Load

rowno()/2 as HalfHour

Autogenerate(48) ;

Then in the chart create the expression :

= sum (  aggr( Statut * Max( if( HalfHour/24 >= Open_Hour and  HalfHour/24  < Close_Hour-0.0001 ,1 , 0) )  , ID, HalfHour) )

For the second question, I think you must begin at 0 not at 1 the autogeneration.

Load time(maketime(rowno()-1)) as HOUR

Autogenerate(24) ;

And change the inequality

sum (  aggr( Max( if( HOUR >= KEY_HOUR_START and  HOUR  <= KEY_HOUR_END ,1 , 0) )  , ID, HOUR) )

jj

swuehl
MVP
MVP

Your TIME_RANGE creation using maketime with rowno() 1 to 24 (with 24 not being accepted by maketime) is why you get the limited display.

Try replacing this with:

TIME_RANGE:

LOAD time((RowNo()-1)/48) as HOUR

Autogenerate(49);

This should create a full 24h range in 30 min increments.

I think you don't need to change anything else since your chart count should be happy with 30 min HOUR intervals.

But since your scheduled time is available in shorter than hour intervals (15 min?), you could also use

Try replacing this with:

TIME_RANGE:

LOAD time((RowNo()-1)/96) as HOUR

Autogenerate(97);

and

time(time#(time_from,'hhmm')) as KEY_HOUR_START,

time(time#(time_to,'hhmm')) as KEY_HOUR_END,

for 15 min HOUR intervals and full KEY_HOUR resolution up to the minutes.

Hope this helps,

Stefan

edit: just noticed Jean-Jacques answer after posting, mine is almost identical (but please note the autogenerate(49) which will create a full 24 hour period from 0:00 to 24:00, or 0 to 1 in numerical representation).

qw_johan
Creator
Creator
Author

Thanks to you both JJ and Stefan. Your answers were VERY helpful.

Thanks again

qw_johan
Creator
Creator
Author

Hi,

Sorry, but I have one more question and I hope you can explain it to me.

My test database holds about 150.000 records (with IDs, dates and times), but only

about 20.000 of them are displayed ?!?...if I load my data with the expression in a table

instead of a chart I can se the total # of records at a specific time. Why is it such

a big difference?...I would think I would see a total of 150.000 records in my table too.

Did I do something wrong...does Qlikview do something I don't understand (like consolidating records etc)?

Any ideas to why?

Included QV for reference.

Thanks

my_image.jpg

swuehl
MVP
MVP

Just select a single ID, e.g. 0012.

You will get 115 records, but your chart will show only max number 1 at any time ID 0012 has worked at any day.

(That's because your chart is using dimension ID in aggr() function).

So if you look at any specific time of the day, a number of people working at a specific time does not necessarily mean they work at the same day at that specific time, just that they ever worked at that time some point in time.

Is this ok for you?

Hope this helps,

Stefan

swuehl
MVP
MVP

I just noticed, that e.g. ID  4018 shows overlapping time periods for the same day. Is this reasonably?

qw_johan
Creator
Creator
Author

Oh ok, thanks for explaining this.

Yes, it is ok for me at this time. I am very happy with the result so far. Also, I can always select a date to see how the chart looks for a particular day.

But, if I later would like to change my application so that for ID 0012 it would show all the 115 records in the chart...would that be possible? How would I do that?

In my application so far I have used aggr() function...is there a better way or a different way to get the same result?

How does the aggr() function perform if I have more data...lets say a couple of million rows? Should my approach be different? (performance, memory usage etc..)

No, overlapping time periods would not be ok in real life...this is just test data and overlapping time periods might exist.

Thanks for all your help,

Johan