Solved: Re: Exclude Holidays in Aging - Qlik Community

israrkhan · ‎2015-10-24

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

MK_QSL · ‎2015-10-24




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;

View solution in original post

MK_QSL · ‎2015-10-24

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...!

ecolomer · ‎2015-10-24

you can calculate by function NetworkDays (DateStart, DateEnd)

israrkhan · ‎2015-10-24

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...

israrkhan · ‎2015-10-24

Dear Enrique,

i created already,

my question is how to exclude holidays?

MK_QSL · ‎2015-10-24

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

israrkhan · ‎2015-10-24

Thanks Manish,

will be waiting for...

maxgro · ‎2015-10-24

PFA

MK_QSL · ‎2015-10-24

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

];

israrkhan · ‎2015-10-24

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..

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

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