Creating periods in time and link dates to it

[#1]

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?

Thanks!

[Anonymous]

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.

[ToniKautto]

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.

[#4]

@ Toni,

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

[ToniKautto]

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.

[#6]

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?

[ToniKautto]

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?

[#8]

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

[Anonymous]

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.

Succes,

[ToniKautto]

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.