17 Replies Latest reply: Dec 11, 2011 7:40 AM by Stefan Wühl RSS

    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

        • Problem displaying scheduled hours
          Jean-Jacques Jesua

          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

            • Re: Problem displaying scheduled hours

              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

                • Problem displaying scheduled hours
                  Jean-Jacques Jesua

                  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

                  • Re: Problem displaying scheduled hours
                    Stefan Wühl

                    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

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

                         

                        Thanks again

                        • Re: Problem displaying scheduled hours

                          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

                            • Problem displaying scheduled hours
                              Stefan Wühl

                              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
                                  Stefan Wühl

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

                                  • Re: Problem displaying scheduled hours

                                    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
                                        Stefan Wühl

                                        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.

                                         

                                        or

                                        =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

                                            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

                                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