topic Re: Time off calculation in QlikView

Time off calculation

Sun, 06 Mar 2016 05:22:17 GMT

Hello,

I need help calculating time off aggregated by week on daily data. The data format is as follows:

Territory ------StartDate-------EndDate ------timeoff

A1---------------02/02/2016-----02/04/2016-----2

A2---------------02/08/2016------02/08/2016---1

Currently i figured out how to calculate time off for a selected range from a calendar object but i am having difficulties calculating time off on a weekly basis. I tried using a Master Calendar but i still couldn't figure it out.

I need the results to look like this:

Territory-------WeekEnding------Timeoff(days)

A1---------------02/07/2016--------2

A2---------------02/14/2016-------1

I tried linking the data to master calendar using Week(StartDate) but didn't work.

Can anyone help? I would appreciate any thoughts and thank you !

Re: Time off calculation

swuehl — Sun, 06 Mar 2016 12:23:29 GMT

Maybe you want to create reference dates for your intervals:

Creating Reference Dates for Intervals

then link the reference dates to your master calendar.

Re: Time off calculation

Sun, 06 Mar 2016 19:30:33 GMT

Thank you swuehl. I will look it up.

Re: Time off calculation

Mon, 07 Mar 2016 19:43:06 GMT

I just tried it and it didn't work. I am still showing missing date ranges (Weeks).

I will try again

Re: Time off calculation

sunny_talwar — Mon, 07 Mar 2016 20:32:28 GMT

May be something like this?

Table:

LOAD *,

WeekEnd(EndDate, 0, 0) as WeekEnding,

EndDate-StartDate as [Time Off];

LOAD * Inline [

Territory, StartDate, EndDate

A1, 02/02/2016, 02/04/2016

A2, 02/08/2016, 02/08/2016

];

Re: Time off calculation

Tue, 08 Mar 2016 12:43:30 GMT

Sunny,

Thank you for your help with this. I tried your suggestion but i am still having a problem with missing dates.

I am working on adding a master calendar to try to fill in the gaps. Below is what i have as of now: