topic Re: Exclude holidays in App Development

Exclude holidays

gp_123 — Thu, 05 May 2022 13:35:47 GMT

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.

Re: Exclude holidays

vinieme12 — Wed, 04 May 2022 12:07:17 GMT

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/DateAndTimeFunctions/networkdays.htm

Re: Exclude holidays

gp_123 — Wed, 04 May 2022 12:38:47 GMT

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

Re: Exclude holidays

vchuprina — Wed, 04 May 2022 13:30:36 GMT

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

Re: Exclude holidays

vchuprina — Wed, 04 May 2022 13:33:29 GMT

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

Re: Exclude holidays

vchuprina — Thu, 05 May 2022 14:07:37 GMT

@gp_123 , please check attached app

Re: Exclude holidays

gp_123 — Thu, 05 May 2022 09:41:51 GMT

Thanks Vitalii.