Exclude Holidays in Aging

[israrkhan]

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]

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

[MK_QSL · Accepted solution]

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;

[Anonymous]

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

[MK_QSL]

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

[Anonymous]

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