topic Re: Date Calculation in QlikView

Date Calculation

Thu, 12 Apr 2012 08:33:15 GMT

Hi!

i have a process which takes 12 hrs to complete from the start date time. So i have start timestamps for different process id's and i want to calculate the time stamp when they will be completed based on 12hrs.

But i dont have to consider weekends and holidays if they fall in between

for eg. if the start date time is 12/04/2012 10:00:00 and 13th is a holiday then i dont have to consider 13 and also 14 and 15 as they are weekends.

thanks in advance

Re: Date Calculation

Thu, 12 Apr 2012 14:37:42 GMT

Hi!

Try the networkdays function:

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

As a startdate you could use the actual startdate, as a enddate you could use the floor(startdate+12h).

Re: Date Calculation

swuehl — Thu, 12 Apr 2012 15:17:14 GMT

I think it's the other way round, ronny123 knows the interval, but wants to get the end date / end timestamp back.

I would first create a calendar with all days, and include also a flag for holidays. Then I would try calculating the number of holidays or week end days to follow directly on any given date for all dates. You can do this by sorting the calendar in reverse order and then use peek() to check for a non-working day and also for accumulating the non-working days.

Then it's all about some logic to add these calculated non-working days on top of your time calculation for your end timestamp:

TMPCalendar:

LOAD

date(makedate(2012)+recno()-1) as Date

AutoGenerate 125;

left join LOAD * INLINE [

Date, Holiday

'06.01.2012', Epiphany

'06.04.2012', 'Good Friday'

'09.04.2012', 'Easter Monday'

];

Calendar:

LOAD *, if(not isnull(Start), timestamp(rangesum(Start,if(hour(Start)>11,AddDay),interval#('12:00:00'))))as End;

LOAD *, WeekDay(Date) as Weekday, Month(Date) as Month, Year(Date) as Year, if(weekday(Date)<5 and isnull(Holiday), Timestamp(Date+RAND())) as Start;

load *, if(weekday(peek(Date))>=5 or not isnull(peek(Holiday)), rangesum(peek(AddDay),1)) as AddDay Resident TMPCalendar order by Date desc;

drop table TMPCalendar;

You would need to replace the 12:00:00 resp. 11 with your process duration time resp. the hour of the day that allows the process still to finish on the same day

Hope this helps,

Stefan

Date Calculation

Thu, 26 Apr 2012 08:25:28 GMT

Hi! Stefan the solution provided by you helped a lot.

I have more condition that working productive hour are only from 9AM to 5 PM . So what happens is my 12 hr (the ideal time to complete the process) completes on next day, but if weekend of or a holiday falls in between, the end date might be some other day than the next day as hours in weekends and holidays will not be considered as productive hours.

So i am not getting the way to by pass the non productive hours of holidays and weekends and calculate the end date from the start date based on 12 hrs.

urgent help required

pls let me know in case you need some more clarification.

Re: Date Calculation

swuehl — Fri, 27 Apr 2012 21:48:04 GMT

ronny123,

if I understood correctly, if you start after 1 pm, your production process will span at least 2 days (max. 4 hours the first day, then full next working day, and rest of the work on the day after). This complicates the things a bit, because you need to check for weekend / holiday twice, and the second check is depending on the outcome of the first check (for example, if you start on friday, you're check will result in next working day is Monday, but then you need to check if the day after Monday is a holiday or not).

You can expand my last example to do this, using a nested peek() function.

Then, since you only have working hours nine to five, the final calculation is also a little bit more complicated, but I think the attached should get what I understood you want.

Have a nice weekend,

Stefan

Re: Date Calculation

Tue, 01 May 2012 13:23:11 GMT

hi! stefan,

thanks for the reply, gr8 help provided by you.

one thing i want to mention is , actually end date in the application sent is not comming aginst the weekends. tickets also come in the system on weekends also but work start on them only on monday after 9 am.so end date is also needed for those cases also. As those cases will also be having a due date time when ideally the work on them should be completed.

in the application attached i am sending the date time when the ticket has come in the system which you can use as a start date in the application.

thanks..

Re: Date Calculation

swuehl — Fri, 04 May 2012 21:44:30 GMT

Ronny123,

what do you mean with 'end date in the application sent is not comming aginst the weekends.'?

Can you give some examples of your expected end date for ticket timestamps e.g. on a saturday morning (before 9 am)?

edit: I assumed that your tickets come in any time, but the work starts only in productive period (working day 9 to 5) and the ideal end time is calculated on top of the next possible start timestamp. See attached (I generalized my first sample app that there can be now unlimited tickets per day, and that first the next possible start timestamp is calculated, and then the ideal end time).

Re: Date Calculation

Mon, 14 May 2012 07:26:50 GMT

Hi! stefan.

thanks a lot for your help and support, the logic provided by you helped a lot. I analysed different permutations and combinations which can exist and used LastWorkDay functions to calulate the end date and finally created a logic which is working fine..

thanks again..:) tc