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!

[#2]

In the meantime I'm trying some data since I have a calender with periodes.

I've updated my SQL statement with no selection on Posting Date but have also put Posting Date in the LOAD statement as Date.

Now I get al the booked hours for the days in the selected period. Great! Exactly what was needed.

The thing is now is, I want to see the total of bookedhours for that period. When I do a sum on that it gives the wrong total. With Period as Dimension (aka 1,2,3 as periodnumber)

I tried Sum of Rows with Date as Dimension and then it shows the right sum. With al the days that have a bookedhours.But I only want to see 1 row with the sum of the period.

Any thoughts?

[Anonymous]

Have you tried using a pivot table with the period as an extra dimension?

[ToniKautto]

Please attach the QVW file with loaded data, to make evaluation of your problem easier.

[#5]

Pivot table works for now.

I'm not fine adding the qvw file since the tables mentioned give the name of the company I work for.

[paulferguson80]

Hi,

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

[#7]

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!

[ToniKautto · Accepted solution]

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.

[paulferguson80]

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.

[#10]

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.

[#11]

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.