Calculating Nth day of month by excluding holidays and weekends

[farheenayesha]

Hi,

I am trying to calculate Nth day of fiscal Month for current and previous year using Invoice date by excluding hoildays and weekends. I want to create two fields as 'Nth Day by Month_PY ' and 'Nth Day by Month_CY'. Please find the data and holiday list attached. Help me to get the output similar to the output sheet in the attached excel.

Invoice Date	Nth Day of Month_PY	Nth Day of Month_CY
12/25/2017	21
12/26/2017	21
12/27/2017	22
12/28/2017	23
12/29/2017	24
12/30/2017	24
12/31/2017	24
12/24/2018		21
12/25/2018		21
12/26/2018		21
12/27/2018		22
12/28/2018		23
12/29/2018		23
12/30/2018		23

[farheenayesha]

I would want the Nth day of the Month CY and PY as below table.  If there is holiday or weekend then the Nth day should show the previous day. 

Ex : Since 30 and 31st Dec is holiday(weekend) hence the nth day for both the days is 24 (Nth day of 29th Dec). Please help me to get these two fields as below.

Invoice Date	Nth Day of Month_PY	Nth Day of Month_CY
12/25/2017	21
12/26/2017	21
12/27/2017	22
12/28/2017	23
12/29/2017	24
12/30/2017	24
12/31/2017	24
12/24/2018		21
12/25/2018		21
12/26/2018		21
12/27/2018		22
12/28/2018		23
12/29/2018		23
12/30/2018		23

[sunny_talwar]

Can you elaborate why the count restart on 10/30/2017 - 10/29/2018 rather than at the beginning of the next month?

[jensmunnichs · Accepted solution]

The way I figured is that the counter restarts in the week that contains the first day of the month, with weeks starting on a Sunday. Afterwards, every weekend or holiday takes the value from the previous day. So the week restarted on 10/29/2017, but the value is taken from the previous day because it's a weekend.

 I also just noticed my reply got deleted for no reason.... PFA*

 I haven't managed to create the exact output he wants, maybe you can solve the join at the end of my script so it doesn't create a row for every combination between dates that have the same nth day?

*Nvm can't attach files for some reason, see script below:

Data:
LOAD [Invoice Date]
FROM
[H:\My Documents\Qlik community\Dummy data.xlsx]
(ooxml, embedded labels, table is Data);

Holidays:
LOAD Concat(chr(39) & Holidays &chr(39),',') AS HD
FROM
[H:\My Documents\Qlik community\Dummy data.xlsx]
(ooxml, embedded labels, table is [Holiday List]);

Let vHoliday = Peek('HD',0,'Holiday');
DROP Table Holidays;

Temp:
LOAD [Invoice Date],
If(WeekStart([Invoice Date], 0, 6) = Weekstart(MonthStart(AddMonths([Invoice Date], 1)), 0, 6),
NetWorkDays(WeekStart([Invoice Date], 0, 6), [Invoice Date], $(vHoliday)),
NetWorkDays(Weekstart(MakeDate(Year([Invoice Date]), Month([Invoice Date]), 1), 0, 6) ,[Invoice Date],$(vHoliday))) as ActualDays
Resident Data;

Drop table Data;

Temp2:
NoConcatenate LOAD [Invoice Date], If(ActualDays = 0, Previous(ActualDays), ActualDays) as ActualDays
Resident Temp;

Drop table Temp;

//Below creates expected output but creates every possible combination between dates with the same nth day, needs more work

//Final:
//LOAD [Invoice Date] as Date2017, ActualDays as [nth Day2017], Month([Invoice Date])&ActualDays as JoinKey
//Resident Temp
//Where Year([Invoice Date]) = 2017;
//
//Outer join
//
//LOAD [Invoice Date] as Date2018, ActualDays as [nth Day2018], Month([Invoice Date])&ActualDays as JoinKey
//Resident Temp
//Where Year([Invoice Date]) = 2018;
//
//Drop field JoinKey;
//Drop table Temp;

[farheenayesha]

Good question Sunny 🙂

I forgot to mention about this. Actually in my logic , if 1st of any month lies within the week of previous month then the entire week is considered as part of next month.

Ex. In the snapshot which you shared. Since 01-Nov-2017 is part of the last week of Oct, hence the entire week is considered as the part of Nov and 29-Oct-2017 is considered as the start date of Nov. Please let me know if you need more clarification.

[farheenayesha]

Thanks for your reply Jensmunnich. Yes you are right. 

[farheenayesha]

Just saw your complete post. Thank you so much for your efforts. Will be waiting for the exact output as i am trying this from long time.

[jensmunnichs]

No problem, sorry for creating a bit of a mess in this thread. I posted all that as a separate reply earlier but for some reason it got deleted... It also won't let me attach .qvw files for some reason, not sure what's going on.

[farheenayesha]

No problem. Appreciate your reply. Even i am facing lot of problems in community because of which i couldn't post this question last week.

[sunny_talwar · Accepted solution]

May be try something like this

Holiday:
Mapping
LOAD Holidays,
	 1
FROM
[..\..\Downloads\Dummy data.xlsx]
(ooxml, embedded labels, table is [Holiday List]);

Data:
LOAD [Invoice Date],
	 MonthName([Invoice Date]) as MonthYear,
	 WeekDay([Invoice Date]) as WeekDay,
	 If(ApplyMap('Holiday', [Invoice Date], Null()) or Match(WeekDay([Invoice Date]), 'Sat', 'Sun'), 1, 0) as HolidayFlag,
	 Day([Invoice Date]) as Day
FROM
[..\..\Downloads\Dummy data.xlsx]
(ooxml, embedded labels, table is Data);

Left Join (Data)
LOAD MonthYear,
	 Max(Day)+1 as Day,
	 1 as MonthEndFlag
Resident Data
Where WeekDay = 'Sun'
Group By MonthYear;

New_Data:
LOAD *,
	 If(MonthYear_New = Previous(MonthYear_New),
	 	If(HolidayFlag = 0, RangeSum(Peek('Nth Day by Month_CY'), 1), Peek('Nth Day by Month_CY')), 1) as [Nth Day by Month_CY];
LOAD *,
	 MonthStart(MonthYear, Alt(MonthEndFlag_New, 0)) as MonthYear_New;
LOAD *,
	 If(IsNull(MonthEndFlag) and MonthYear = Previous(MonthYear), Peek('MonthEndFlag_New'), MonthEndFlag) as MonthEndFlag_New
Resident Data
Order By [Invoice Date];

DROP Table Data;