Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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).
Thanks to you both JJ and Stefan. Your answers were VERY helpful.
Thanks again
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
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
I just noticed, that e.g. ID 4018 shows overlapping time periods for the same day. Is this reasonably?
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