Calculate working days (excluding weekends and Holidays) in qliksense

[Aspiring_Developer]

Hi All,

I have to calculate working days (excluding weekends and holidays )  with below data:-

issue_receieved_date -- will be start date

logged_date-- will be end date

I have been provided with the list of holidays

Holidays:
Load * inline [

04/10/2020
04/13/2020
05/04/2020
05/25/2020
08/31/2020
12/25/2020
12/28/2020
01/01/2021
04/02/2021
04/05/2021
05/03/2021
05/31/2021
08/30/2021
12/27/2021
12/28/2021
01/03/2022
04/15/2022
04/18/2022
05/02/2022
06/02/2022
06/03/2022
08/29/2022
12/26/2022
12/27/2022
01/02/2023
04/07/2023
04/10/2023
05/01/2023
05/29/2023
08/28/2023
12/25/2023
12/26/2023

];

I am able to calculate networking days  using networkdays()  but how should I include this holiday list as well to create my working days fields in qliksense.

Can anyone please help ??

@sunny_talwar @Kushal_Chawda 

Thanks in advance

[brunobertels]

Hi 

no time to test but below is what i would try 

Create a dummy calendar from your start date to your end date 

Or if already exist , reload in a temptable those date.

Then select only those dates = to weekend with a where clause 

join the result to your holidays table 

concat the result and put it in a variable 

at the end use networkday function and use the variable as Holliday parameters 

[Aspiring_Developer]

Hi @brunobertels 

Thanks for your response.

However it is not very clear for me as I am new in qlik. Could you please elaborate with a sample code ?

Thanks

[brunobertels]

Hi 

Try this 

set vHolidays =concat(Holidays,',');

Holidays:
Load * inline [
Holidays
04/10/2020
04/13/2020
05/04/2020
05/25/2020
08/31/2020
12/25/2020
12/28/2020
01/01/2021
04/02/2021
04/05/2021
05/03/2021
05/31/2021
08/30/2021
12/27/2021
12/28/2021
01/03/2022
04/15/2022
04/18/2022
05/02/2022
06/02/2022
06/03/2022
08/29/2022
12/26/2022
12/27/2022
01/02/2023
04/07/2023
04/10/2023
05/01/2023
05/29/2023
08/28/2023
12/25/2023
12/26/2023

];

///// then your actual table here 

[Table]:
LOAD
...
Date(Date#(YourDateField, 'DD/MM/YYYY') ) AS YourDateField
FROM .....
(biff, embedded labels, table is Feuil1$);

WeekEnd:
concatenate(Holidays) Load
distinct
Date(Date#(YourDateField, 'DD/MM/YYYY') ) AS [Holidays]
resident [Table]
where num(weekday(YourDateField))>4;

Then in front end use :

networkdays(YourDateField,$(vHolidays))

hope it helps 

[Aspiring_Developer]

Hi @brunobertels 

Thanks a lot for explaining it.

I have to calculate network days on two dates i.e

Networkdays (issue_recd_date, logged date,),

So i have to create two date field in my main table (one for issue recd date , other for logged date)and then which date field should be used in where clause ?

[Aspiring_Developer]

Can anyone please please help me with this ? 

[brunobertels]

Hi 

how do you define issue rec date and logged date ? 

does all the dates exist ( I mean 365 dates for a year) i your data model ? 

[Aspiring_Developer]

Hi @brunobertels 

Isse red date is when the issue was received and logged date is when the issue was logged .

Both these dates have historical data starting from 2013 till now. 

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  did you try below

Holidays_table:
LOAD Concat(Holidays,',') as Holidays_value;
Load * inline [
Holidays
04/10/2020
04/13/2020
05/04/2020
05/25/2020
08/31/2020
12/25/2020
12/28/2020
01/01/2021
04/02/2021
04/05/2021
05/03/2021
05/31/2021
08/30/2021
12/27/2021
12/28/2021
01/03/2022
04/15/2022
04/18/2022
05/02/2022
06/02/2022
06/03/2022
08/29/2022
12/26/2022
12/27/2022
01/02/2023
04/07/2023
04/10/2023
05/01/2023
05/29/2023
08/28/2023
12/25/2023
12/26/2023
];

let vHolidays = Peek('Holidays_table',0,'Holidays_value');

Data:
LOAD *,
     Networkdays (issue_recd_date, logged date,'$(vHolidays)')
FROM Table ;

[Aspiring_Developer]

Hello @Kushal_Chawda 

Thank you very much for the solution. I tried and it is almost working.

However , i am facing some issue is network days formulae. I have below output:-

between 23/12/2021 and 06/01/2021 - i have 3 holidays (27/12/2021','28/12/2021','03/01/2021') so my working days should be 8 and not 11.  This log_working days field is derived from the above solution.

I created a new measure in the table and manually inputted the above holiday dates in network days formulae and it is giving me correct number.

Why my backend logic field is not counting my holiday table ?

Can you please help ?