20 Replies Latest reply: May 25, 2011 10:30 AM by Erica de+Boer RSS

    Creating periods in time and link dates to it

      Hi all,


      I am developing an application which should show the booked working hours for employees. The interval of these booked hours is a determined period.

      For 2011 these are the dates on which the hours booked must be shown:

      period 1: 27-12-2010 till 23-01-2011

      period 2: 24-01-2011 till 20-02-2011

      period 3: 21-02-2011 till 23-03-2011 and so on.



      To retrieve the hours from the database in SQL, I used the Posting Date Field in the database. And just put: " where Posting Date between '2011-02-21' and '2011-03-27'. Ofcourse that works. But it's not very usefull to change this for every period applicable.


      Is there a way that I can use the period dates above to automatically select the right amount of rows from the database? Instead of changing this by hand every time?


































        • Creating periods in time
          Dennis Hoogenboom

          I am not an expert on how to creat this in the script so if I would have to make this I would just create a Excel Sheet which contains all the dates of the year(s) and in the second colomn the period name.

          If you load this in your Qlikview document you can use.

          • Creating periods in time
            Toni Kautto

            I would not solve this in the SQL statement, but instead use a master calender table to mark the periods. In the same manner as dates are related to Quarters you would create dates related to your periods.

              • Creating periods in time

                @ Toni,


                Could you give me a headstart on how I do this?

                  • Re: Creating periods in time
                    Toni Kautto

                    Find the attached simple Master Calendar, with a custom period filed added. In this case each period will be 28 days (vHourBookingPeriodLength) starting from the calender's starting date(vCalendarStart ).


                    Probably you need to find a way to calculate for a restart of period for each year, as I guess that is the preferred numbering. Still the principle by linking your data dates to a calendar should be the nicest solution for making data linked to time periods.

                      • Creating periods in time

                        That is a very good help, thanks!


                        The thing is, the number of weeks in a period differs. It's 4,4,5 everytime. So period 1 and 2 are 4 weeks, period 3 is 5 weeks.

                        Any thoughts about that?

                          • Re: Creating periods in time
                            Toni Kautto

                            That is a bit tricker, but not impossible.


                            You need to specify the period rule a bit more in detail, meaning what the logic behind it is. For a year with 52 weeks you will have 13 periods, but for years with 53 weeks you will have an extra week. Is the 5 weeks period to handle this extra week? Or is the pattern periodical like 445445445445445 etc? Is the period numbering also cyclic, so that it restarts on 1 after a determined period or is it never ending?

                              • Creating periods in time

                                The pattern is periodical:


                                Period Number of Weeks Period Start Period End
                                1 4 27-12-10 23-01-11
                                2 4 24-01-11 20-02-11
                                3 5 21-02-11 27-03-11
                                4 4 28-03-11 24-04-11
                                5 4 25-04-11 22-05-11
                                6 5 23-05-11 26-06-11
                                7 4 27-06-11 24-07-11
                                8 4 25-07-11 21-08-11
                                9 5 22-08-11 25-09-11
                                10 4 26-09-11 23-10-11
                                11 4 24-10-11 20-11-11
                                12 5 21-11-11 25-12-11



                                Also is restarts on 1 every year. For example, these were the periods for 2010.


                                Period Number of Weeks Period Start Period End
                                1 4 28-12-09 24-01-10
                                2 4 25-01-10 21-02-10
                                3 5 22-02-10 28-03-10
                                4 4 29-03-10 25-04-10
                                5 4 26-04-10 23-05-10
                                6 5 24-05-10 27-06-10
                                7 4 28-06-10 25-07-10
                                8 4 26-07-10 22-08-10
                                9 5 23-08-10 26-09-10
                                10 4 27-09-10 24-10-10
                                11 4 23-10-10 21-11-10
                                12 5 22-11-10 26-12-10
                      • Re: Creating periods in time
                        Dennis Hoogenboom

                        Because the "rule" is not the same for every year you have to write it down somewhere, or in your script or somewhere else. I just created an Excel file as an example and connected it to a master calander.


                        Hopelijk heb je er iets aan.



                          • Re: Creating periods in time
                            Toni Kautto

                            Running it via Excel can be a good way to get the system going by applying your intended data model, but in the long run I would definitely generate it automaytically in the script. However due to the logics behind your periods, it likley need to be created in a separate table first and then joined with the calendar. With some FOR loops and variables I am sure this can be accomplished in the script.


                            As said it is a bit tricky, so no permanent solution to offer right now. I will try to get back with a suggestion later on.


                            Please mark mine and Dennis entries as helpful in mean time, to show that the thread is going towards a final solution.

                          • Re: Creating periods in time
                            Paul Ferguson


                            I spotted this post from your post about the loop in your data and thought this approach may help you to build the calendar based on what you want and remove the loop caused by department.

                            I have taken the Calendar table that you posted for the periods built it around the start and end dates from there.

                            This removes the table BlaBla and should get rid of the loop in your second post.


                            I have attached the sample code and excel file (i have re engineered Dennis post)


                            Hope this helps

                              • Creating periods in time

                                Hi Paul,


                                Thanks so much for your energy in this and willing to help the issues with me.


                                I have tried your example, but it still gives me a loop.


                                When I only use the mastercalender and load the billable hours with their respective departments out of the database, everything is going fine.

                                When I add the Excel file with the FTE to their departments and periods, the loop kicks in.


                                I am able to get the FTE out of the database with their departments without any problems. Loops in this case.


                                The thing I cannot figure out is how to link the FTE to the right period.

                                When I want to see the report from the server I fill in that the OutofEmployment_Date must be in the period and the same for Employment_Date to get the changed records.


                                I am looking for a way to compare the OutofEmployment_Date and Employment_Date to the Date from the Calender (which is linked to the periods). That way only the employees that were Employed in that period are counted for the FTE.


                                I tried something with Min(Date) and Max(Date) and it does something for the Departments who had changing FTE in 2011, but I cannot get it right as of yet.


                                I believe that working on a relation/link between the employment dates and the perioddate is the best solution. Maybe not the most simple one. But I have a feeling it can be done.


                                So if anyone has a thought about that, let me know!

                                  • Re: Creating periods in time
                                    Toni Kautto

                                    Sorry for my slowness. After considering what the easiest way to solve this is, I would say that a mapping table is quickest to implement. In the attached sample, you can find that the PeriodMap sets the periods on a weekly basis.


                                    I would not recommend adding the month names as you have done in the previous example. Why dont you just modify the MonthName variable in the script if you want to have long month names? I updated my example to also cover this naming need.

                                      • Re: Creating periods in time

                                        My loop is gone!


                                        Thanks to the solution of Toni by making my Period in Weeks, I don't get a loop now.


                                        I have loaded the FTE and the Booked Work hours in Qlikview, so now the question is; how can I only calculate the Contracthours of the Employees that are employed in the selected period and therefore exclude the ones that were Out of Employment or not yet Employed.


                                        I have attached the qvw as it is now.

                                      • Re: Creating periods in time
                                        Paul Ferguson

                                        Hi Again,


                                        Im hoping i have correctly grasped what you want to do with the FTE's and the data you can bring in but here is a go for you:

                                        Employment_Date (start of Employment) to OutofEmployment_Date (End of Employment) for a person.

                                        So Person1 - Employment_Date = 01/01/2011 and OutofEmployment_Date = 10/01/2011 your FTE  would be 10 in period 1 of 2011?

                                        The interval match process in qlikview takes your start and end dates for person1 and matches them to the date on the calendar that has the Period matched already.

                                        I have attached your example to show the principal.


                                        From your other post i believe this removes the need for Periode to be on your FTE table and removes the loop allowing the join on the Master_Calendar to deal with this for you if you know what department a person would be in?


                                        Also if you need to create a link for department, i would use the match process to create a secondary key on the Master_Calendar to of Date&Department which will allow implicit joining.


                                        Hope this helps if not directly with solving the problem overall, it may just give you some ideas of how to move forward with this problem.

                                          • Creating periods in time

                                            Hi Paul,


                                            Thanks so much for this, you really are helping me.


                                            The thing about the FTE: The FTE is counted by summing up the Contracthours of the Employees divided by 40 (workhours per week).

                                            But only the contracthours of the Employees that are employed in that period should be counted.

                                            For example: Period 3 starts at 21-2-2011 and ends 27-3-2011. Period 4 starts at 28-3-2011 and ends at 24-4-2011.

                                            In the meantime 1 person leaves the company at 27-3-2011. So he has a OutofEmployment_Date of 27-3-2011.

                                            His contracthours (40) should be in the sum for Period 3 but not for Period 4.


                                            That said; With the Employment_Date, the Out_of_Employment_Date, the Department and Contracthours are being loaded from the database.


                                            But the application has to have more information: The number of hours worked by the Employees. With again the Department and the PostingDate of the worked hours. The PostingDate also has to be in that period.


                                            With all these dates and the Departments of the Employees and their worked hours, I get a loop.