topic Re: Calculate working days (excluding weekends and Holidays) in qliksense in App Development

Calculate working days (excluding weekends and Holidays) in qliksense

Aspiring_Developer — Wed, 16 Feb 2022 14:20:56 GMT

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

Re: Calculate working days (excluding weekends and Holidays) in qliksense

brunobertels — Wed, 16 Feb 2022 13:06:14 GMT

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

Re: Calculate working days (excluding weekends and Holidays) in qliksense

Aspiring_Developer — Wed, 16 Feb 2022 13:41:10 GMT

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

Re: Calculate working days (excluding weekends and Holidays) in qliksense

brunobertels — Wed, 16 Feb 2022 18:04:54 GMT

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

Re: Calculate working days (excluding weekends and Holidays) in qliksense

Aspiring_Developer — Wed, 16 Feb 2022 18:11:02 GMT

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 ?

Re: Calculate working days (excluding weekends and Holidays) in qliksense

Aspiring_Developer — Thu, 17 Feb 2022 13:17:03 GMT

Can anyone please please help me with this ?

Re: Calculate working days (excluding weekends and Holidays) in qliksense

brunobertels — Thu, 17 Feb 2022 16:37:32 GMT

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 ?

Re: Calculate working days (excluding weekends and Holidays) in qliksense

Aspiring_Developer — Thu, 17 Feb 2022 17:56:26 GMT

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.

Re: Calculate working days (excluding weekends and Holidays) in qliksense

Kushal_Chawda — Sat, 19 Feb 2022 15:51:48 GMT

@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 ;

Re: Calculate working days (excluding weekends and Holidays) in qliksense

Aspiring_Developer — Mon, 21 Feb 2022 10:43:39 GMT

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 ?