topic Re: Exclude Holidays in Aging in QlikView

Exclude Holidays in Aging

israrkhan — Sat, 24 Oct 2015 15:40:52 GMT

Hi Guys,

i want to exclude Holidays in aging?

i have Request submit_date and Resolved_Date, i created aging in script by using if statements. that is simple...

now i want to exclude holidays between Submit_date and resolved_date,

i want to have holidays in a excel file, weekend and public holidays..weekend is Friday in my case...

kindly share your thought, what is the best approach to achieve this requirement,

what type of template should i use?

your help will be much appreciated.

Many Thanks

Re: Exclude Holidays in Aging

MK_QSL — Sat, 24 Oct 2015 15:47:28 GMT

Use NetworkDays Function...

Create a Variable which will contains all the public holidays and holidays..

If you provide few lines of your sample code along with some holidays which are getting covered in those Request and Resolve Date, I would work for you.

I have done something similar in my current project two weeks before...!

Re: Exclude Holidays in Aging

ecolomer — Sat, 24 Oct 2015 17:02:52 GMT

you can calculate by function NetworkDays (DateStart, DateEnd)

Re: Exclude Holidays in Aging

israrkhan — Sat, 24 Oct 2015 17:09:39 GMT

Hi Manish,

i got the idea,

let say i have

Request ID, SUBMIT_date, Resolved Date

BY USING Submit_date - Resolved_date i can create the age in days.

then next step is to load holidays from excel,

my questions are:

1) what columns do i need to create in excel?

2) how to link with Request Table, do i need to link holidays table with request table or not?

3) let say i have holidays for 1 year in excl file, which will be around 80, 90 days,

may i keep these all dates in variable?

4) in which format do i need to have these dates in variables?

Many Thanks...

Re: Exclude Holidays in Aging

israrkhan — Sat, 24 Oct 2015 17:10:56 GMT

Dear Enrique,

i created already,

my question is how to exclude holidays?

Re: Exclude Holidays in Aging

MK_QSL — Sat, 24 Oct 2015 17:18:50 GMT

I will upload a sample file and solution file both soon. You can use it as a sample for your application.

Re: Exclude Holidays in Aging

israrkhan — Sat, 24 Oct 2015 17:20:57 GMT

Thanks Manish,

will be waiting for...

Re: Exclude Holidays in Aging

maxgro — Sat, 24 Oct 2015 17:30:35 GMT

PFA

Re: Exclude Holidays in Aging

MK_QSL — Sat, 24 Oct 2015 17:31:00 GMT

Holiday:

//This holidays is for demo purpose only. You can use Excel file!

Load * Inline

[

	HolidayDate
	02/10/2015
	14/10/2015
	27/10/2015

];

TempConcat:

LOAD Concat(CHR(39) & HolidayDate & CHR(39),',',HolidayDate) as HolidayDates Resident Holiday;

Let vHolidays = FieldValue('HolidayDates',1);

Drop Table Holiday;

Drop Table TempConcat;

Data:

Load

	RequestID,
	SubmitDate,
	ResolvedDate,
	NetWorkDays(SubmitDate, ResolvedDate, $(vHolidays)) as AgeinDays	//This will excludes Holidays and Saturday and Sundays.

Inline

[

RequestID,	SubmitDate, ResolvedDate
1,		01/10/2015,	13/10/2015
2,		05/10/2015,	30/10/2015
3,		12/10/2015, 28/10/2015

];

Re: Exclude Holidays in Aging

israrkhan — Sat, 24 Oct 2015 17:44:22 GMT

Thank You Manish,

explained very well.

but one point is missing,

we have just one holiday which is Friday. Saturday and Sunday are working days..

how to handle this.

and do i need to put Friday Dates in excel file or only public holidays...?

thanks once again..

Re: Exclude Holidays in Aging

MK_QSL — Sat, 24 Oct 2015 18:02:33 GMT

I will provide that after 3 hours. I am currently now outside !

Re: Exclude Holidays in Aging

MK_QSL — Sat, 24 Oct 2015 21:02:24 GMT




Holiday:
//This holidays is for demo purpose only. You can use Excel file!



Load * Inline
[




HolidayDate



02/10/2015



14/10/2015



27/10/2015



];


TempConcat:
LOAD Concat(CHR(39) & HolidayDate & CHR(39),',',HolidayDate) as HolidayDates Resident Holiday;


Let vHolidays = FieldValue('HolidayDates',1);


Drop Table Holiday;
Drop Table TempConcat;


Data:
Load




RequestID,



SubmitDate,



ResolvedDate,



NetWorkDays(SubmitDate, ResolvedDate, $(vHolidays)) as AgeinDays
//This will excludes Holidays and Saturday and Sundays.



Inline
[




RequestID,
SubmitDate, ResolvedDate



1,


01/10/2015,
13/10/2015



2,


05/10/2015,
28/10/2015



3,


12/10/2015, 28/10/2015



];


TempData:
Load




RequestID,



Date(SubmitDate + IterNo() - 1) as SubmitDateNew,



Date(SubmitDate + IterNo()) as ResolvedDateNew



Resident Data
While SubmitDate + IterNo() - 1 <= ResolvedDate;


Left Join (Data)
Load




RequestID,



Interval(SUM(ResolvedDateNew)-SUM(SubmitDateNew)-1,'D') as TotalAge



Resident TempData
Where Num(WeekDay(SubmitDateNew)) <> 4 and Not Match(SubmitDateNew,$(vHolidays)) 
Group By RequestID;


Drop Table TempData;

Re: Exclude Holidays in Aging

Anonymous — Sun, 25 Oct 2015 08:01:17 GMT

I have a similar requirement but not getting the value in Total age. Also, I am considering time as well. Like, you can see my below example the interaction started at 04:50 PM on 1st May and ended on 15/05/2015 at 12:33 PM.

I have applied your script but not getting Total age. Also, i want to get the total hours take for this request.

Could you please suggest me what i am doing wrong?

RequestID	Start Time	Resolved Date	TotalAge
11414300	1/5/2015 4:50:39 PM	15/05/2015 12:33:31 PM

Regards,

Imran K

Re: Exclude Holidays in Aging

MK_QSL — Sun, 25 Oct 2015 15:26:14 GMT

what is the result you are looking for your this example?

Re: Exclude Holidays in Aging

Anonymous — Sun, 25 Oct 2015 16:07:24 GMT

Thanks Manish. I have sorted it out by my own.

	RequestID,
	Date(SubmitDate + IterNo() - 1) as SubmitDateNew,
	Date(SubmitDate + IterNo()) as ResolvedDateNew

	RequestID,
	Interval(SUM(ResolvedDateNew)-SUM(SubmitDateNew)-1,'D') as TotalAge