topic Difference of dates based on irregular calendar in QlikView

Difference of dates based on irregular calendar

miikkaqlick — Mon, 03 Oct 2011 09:36:13 GMT

Hi!

I'm making production time report. In report I'm calculating how long it takes to manufacture different products. Starting datetime is when order arrives and end datetime is when invoice is formed. I made this and application works fine, but customer has sometimes one shift working on weekdays and sometimes they have three shifts working all week.

So basically calendar is totally user defined.

For example:

Order arrives Invoice is formed

2011-09-30 12:00 2011-10-03 12:00

Case 1:

Only one shift working on weekdays. Correct answer is 8h. From 2011-09-30 12:00 to 2011-09-30 16:00 and from 2011-10-03 08:00 to 2011-10-03 12:00.

Case 2:

Three shifts working whole week. Correct answer is 72h.

In real life they might have one shift working from monday to wednesday, 2 shifts working thurday and friday and plant closed on weekend.

Now I'm using intervall-function. To my knowledge you can add holidays to networkingdays-function but you cannot add extra working dates.

My data looks like this:

Order Category StartTime EndTime

1 11 2011-09-14 13:15:08 2011-09-18 12:34:16

2 12 2011-09-14 13:16:10 2011-09-14 15:50:30

Re: Difference of dates based on irregular calendar

sbaldwin — Tue, 04 Oct 2011 15:05:03 GMT

Hi, have you considered loading your calendar table as a data island (or loosly coupled if you have different shift patterns for different customers then sum up the shift hours between the start and end times, i have attached a very basic example

Re: Difference of dates based on irregular calendar

miikkaqlick — Wed, 05 Oct 2011 06:27:11 GMT

Thanks for answer!

Your solution is partial solution. It doesn't take times to account. Customer has some products that are really fast to manufacture. So if order comes early in morning, it's ready in evening. In this case your solution shows 8h.

Idea is great. I think I'll try to modify it a bit so that it takes also times to account.