Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
israrkhan
Specialist II
Specialist II

Exclude Holidays in Aging

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

14 Replies
MK_QSL
MVP
MVP

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
Master II
Master II

you can calculate by function NetworkDays (DateStart, DateEnd)

israrkhan
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

Dear Enrique,

i created already,

my question is how to exclude holidays?

MK_QSL
MVP
MVP

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

israrkhan
Specialist II
Specialist II
Author

Thanks Manish,

will be waiting for...

maxgro
MVP
MVP

PFA

MK_QSL
MVP
MVP

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
Specialist II
Specialist II
Author

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