Exclude holidays

[gp_123]

Hello all,
I need to calculate 'Pending for' field which has formula in excel as  : 

IF([@Area]="Capgemini",NETWORKDAYS.INTL([@[TS Opening Date]],TODAY(),11,Holidays!$C$2:$C$11)-1,NETWORKDAYS.INTL([@[TS Opening Date]],TODAY(),1,Holidays!$C$12:$C$37)-1)

I need to transform it in qlik. I am providing sample data. 

Thanks.

[vinieme12]

Refer the help section, this has good examples on how to use Networkdays in qliksense

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[gp_123]

Okay thanks for the reply. But I am stucked at Here "Holidays!$C$2:$C$11)-1".
I am not getting how to map this in qlik sense

[vchuprina]

Hi, 

Holidays!$C$2:$C$11 means that you should load Data from sheet Holidays, column C and from cell C2 till C11

Regards,

Vitalii

[vchuprina · Accepted solution]

Check my script, 

First, you should create a list of holidays, you can create them by using the Concat function.

After this, you should store a list of holidays into a variable and use it in the NetWorkDays function

Holidays:
LOAD
      Country,
      Concat(chr(39)&Date([Date/Month])&chr(39), ',') AS HolidayPeriod
FROM
[C:\Users\chuprynav\Downloads\Data (1).xlsx]
(ooxml, embedded labels, table is Holidays)
Group By Country;

HolidaysCampgemini:
NoConcatenate
LOAD
      Country,
      HolidayPeriod
Resident Holidays
Where Country = 'India';

LET vCampgemini = Peek('HolidayPeriod', 0, 'HolidaysCampgemini');

DROP Table HolidaysCampgemini;

HolidaysOther:
NoConcatenate
LOAD
      Country,
      HolidayPeriod
Resident Holidays
Where Country <> 'India';

LET vOtherHolidays = Peek('HolidayPeriod', 0, 'HolidaysOther');

DROP Tables HolidaysOther, Holidays;

Data:
LOAD Workflow,
     [Range Request #],
     [Workflow Created By],
     Workitem,
     [Workitem Status],
     [WF Opening Date],
     [TS Opening Date],
     [WI Selected],
     [WI Task],
     [WF Form],
     AGI,
     Class,
     Plant,
     User,
     [Last Name],
     [First Name],
     [WI Text],
     [Concat Task],
     [Responsible for the task],
     Area,
     IF(Area = 'Capgemini', NetWorkDays([TS Opening Date], Today(), $(vCampgemini)) -1,
     NetWorkDays([TS Opening Date], Today(),  $(vOtherHolidays))-1) as NetWorkDays,
     [Pending For]
FROM
[C:\Users\chuprynav\Downloads\Data (1).xlsx]
(ooxml, embedded labels, table is [Open WF]);

Regards,

Vitalii

[vchuprina]

@gp_123 , please check attached app

[gp_123]

Thanks Vitalii.