Solved: Re: Exclude only Public Holidays irrespective of S... - Qlik Community

raajaswin · ‎2017-09-28

Hi all,

While Calculating Ageing between two dates can we exclude only specified holidays? (i.e) I need to exclude only the list of dates specified irrespective of saturday or sunday. when I use Network days and specify the list of holidays in a variable. networkdays automatically excldues saturday, sunday also. Pls suggest an alternate approach

prat1507 · ‎2017-09-29

HI aswin, =Div(WEEKDAY(day('2017-08-31')-6)+1-date('2017-08-31')+date('2017-09-25'),7)

returns 4, which is correct, it is the no. of saturdays.

If you want to exclude only sundays and holidays, use

Networkdays() = your expression which excludes Sat,Sun,Holiday + my exp which returns no. of Sats

so the above exp will only exclude holidays and sundays. I hope you understand now.

Regards
Pratyush

View solution in original post

prat1507 · ‎2017-09-28

Add no. of saturdays and sundays in the date range.

Regards
Pratyush

prat1507 · ‎2017-09-28

Just add this to your expression

date('2017-09-28')-date('2016-09-28')-NetWorkDays('2016-09-28','2017-09-28')

replace '2017-09-28' with your end date and '2016-09-28') as your start date

Regards
Pratyush

raajaswin · ‎2017-09-28

if i need saturdays.. i need to exclude only sunday and public holidays then?

prat1507 · ‎2017-09-29

Maybe this,

=Div(WEEKDAY(date('2017-10-07')-6)+1-date('2017-09-22')+date('2017-10-07'),7)

replace '2017-10-07' with your end date and '2016-09-22') as your start date

raajaswin · ‎2017-09-29

No this gives wrong number of sundays for many cases. Any other possible way

Kushal_Chawda · ‎2017-09-29

How do you recognize holidays? is there any flag in your data for holidays?

prat1507 · ‎2017-09-29

This is used for Saturdays. You asked me for Saturdays.

Regards
Pratyush

raajaswin · ‎2017-09-29

list of Holidays will be maintained in a table or excel file

raajaswin · ‎2017-09-29

=Div(WEEKDAY(date('2017-09-25')-6)+1-date('2017-08-31')+date('2017-09-25'),7)

check for these dates.

The Exact Requirment is to Exclude Sundays and Public Holidays..

Eg:

Date 1 : 01/09/2017

Date 2 : 25/09/2017

say 05/09/2017 is only puclic holiday falling under this date range and 3,10,17,24/09/2017 are the sundays in those range

Expected result : 20 Days ( Total 25 days - (4 Sundays + 1 public holiday))

Exclude only Public Holidays irrespective of Saturday Sunday