topic Problem displaying scheduled hours in QlikView

Problem displaying scheduled hours

qw_johan — Sun, 13 Nov 2011 11:54:18 GMT

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

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

Problem displaying scheduled hours

Sun, 13 Nov 2011 13:32:21 GMT

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.

Re: Problem displaying scheduled hours

qw_johan — Sun, 13 Nov 2011 17:09:15 GMT

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.

****** 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

Problem displaying scheduled hours

Sun, 13 Nov 2011 17:35:13 GMT

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

Re: Problem displaying scheduled hours

swuehl — Sun, 13 Nov 2011 17:42:45 GMT

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

Re: Problem displaying scheduled hours

qw_johan — Sun, 13 Nov 2011 19:44:29 GMT

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

Thanks again

Re: Problem displaying scheduled hours

qw_johan — Mon, 14 Nov 2011 21:21:39 GMT

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

Problem displaying scheduled hours

swuehl — Mon, 14 Nov 2011 21:42:13 GMT

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

Problem displaying scheduled hours

swuehl — Mon, 14 Nov 2011 21:46:19 GMT

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

Re: Problem displaying scheduled hours

qw_johan — Tue, 15 Nov 2011 16:13:32 GMT

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

Re: Problem displaying scheduled hours

swuehl — Tue, 15 Nov 2011 16:51:05 GMT

I'm not sure if you need the aggr(). Probably not.

You may want to play around with

=count(DISTINCT if( HALFHOURS >= my_time_from and HALFHOURS < my_time_to ,ID ))

Here you should get the same result as with your aggr() function. count(distinct ..) is not very good performing, but there are some methods to overcome this.

For example, you could create a separate table with only unique IDs and a second field IDCounter with constant value 1. Then use a sum on this counter instead the count distinct.

=sum( if( HALFHOURS >= my_time_from and HALFHOURS < my_time_to ,1,0 ))

This should aggregate over all records, so if you select only one ID, you'll see the time of day where this ID worked frequently or only randomly.

Please see attached.

Stefan

Re: Problem displaying scheduled hours

qw_johan — Tue, 15 Nov 2011 20:27:30 GMT

Thanks. It was very interesting to see the difference between the expressions.

And thanks for explaining them.

The count(DISTINCT ..... does seem to give the same result as aggr().

I was especially surprised when I saw what this expression did:

=sum( if( HALFHOURS >= my_time_from and HALFHOURS < my_time_to ,1,0 ))

This is probably an even better way of doing what I want...looks like the result is "better".

//Johan

Problem displaying scheduled hours

Wed, 23 Nov 2011 07:33:37 GMT

hi qv_johan

I´ve started an similar discussion of how many people were in a building at a specific time and it looks like your problem here is somewhat the same.

I got one question for you - what happens if there is a time that crosses a date ex.

mydatestart mytimestart mydateend mytimeend

2011-11-11 23:00 2011-11-12 02:00

could you please attach your final qvw file

Re: Problem displaying scheduled hours

qw_johan — Wed, 23 Nov 2011 19:53:47 GMT

Hi,

The final qvw file is the one that swuehl posted here. I add it to this reply too.

Sorry...but time that crosses a date haven't been a problem to me so I haven't thought about that.

But my guess is that it would just stop at midnight...not cross over and continue next date.

Re: Problem displaying scheduled hours

swuehl — Wed, 23 Nov 2011 23:04:46 GMT

No, I think that these records will not be taken correctly into account.

If you look at the condition

if( HALFHOURS >= my_time_from and HALFHOURS < my_time_to ,1,0 )

and if you replace my_time_from and my_time_to with actual hours, in most records that will cross a date, my_time_to will be smaller than my_time_from, so above AND condition could not be fulfilled.

You could certainly work around this using additional check for a date crossing.

Re: Problem displaying scheduled hours

Thu, 24 Nov 2011 07:28:21 GMT

swuehl -

I posted a discussion some days ago and one several weeks ago but nobody seems to have a clue to solve my problem.

the post is : How many people were/are in the building.

Somewhat the same as this displaying scheduled hours.
For me I just want to calculate how many people is in the building a a specific time - using a login and logout data - which can spread over several days

Re: Problem displaying scheduled hours

qw_johan — Sun, 11 Dec 2011 08:19:14 GMT

Hi,

I am very happy with my application so far. But now i am trying to figure out how to check

for date crossing. Would I check for that inside this expression with another if condition?

if( HALFHOURS >= my_time_from and HALFHOURS < my_time_to ,1, NEW IF CONDITION HERE 0 )

Problem displaying scheduled hours

swuehl — Sun, 11 Dec 2011 12:40:50 GMT

Hi,

both John Witherspoon and me created different working solutions for polschou's similar request, so you just want to have a look into that:

http://community.qlik.com/thread/38841

Regards,

Stefan