topic Re: Exclude days in an expression using a field in QlikView

Exclude days in an expression using a field

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Is there a way to exclude certain dates in an expression without having to type them out every time?

To calculate the amount of working days in a month, I use the following expression:

=NetWorkDays(MakeDate(only(Year),only(Month),1),MonthEnd(MakeDate(only(Year),only(Month),1)), '11/11/2015', '25/12/2015', '01/01/2016', '28/03/2016')

But I would like to use some kind of new field containing all of the dates I would like to exclude, so I could use something like:

=NetWorkDays(MakeDate(only(Year),only(Month),1),MonthEnd(MakeDate(only(Year),only(Month),1)), 'holidays')

With the new field being: holidays = '11/11/2015', '25/12/2015', '01/01/2016', '28/03/2016'

Is that possible?

Thanks in advance!

Re: Exclude days in an expression using a field

Gysbert_Wassenaar — Tue, 15 Mar 2016 10:12:59 GMT

You can create a variable that contains the list of holidays:

SET vHolidays = '11/11/2015', '25/12/2015', '01/01/2016', '28/03/2016';

You can then use that variable in the expression

=NetWorkDays(MakeDate(only(Year),only(Month),1),MonthEnd(MakeDate(only(Year),only(Month),1)),$(vHolidays))

If you have a table that contains the holiday dates you can collect them from there:

Temp:

LOAD concat(distinct chr(39 & MyHolidayDate & chr(30)) as HolidayList From MyHolidayTable;

LET vHolidays = peek('HolidayList');

Drop Table Temp;

Re: Exclude days in an expression using a field

swuehl — Tue, 15 Mar 2016 10:13:14 GMT

In this specific case, you can't use a fied for the holidays, it needs to be a comma separated list of arguments.

You can use a variable that dollar-sign expand to this argument list:

Open variable overview and create a new variable vHolidays with value (no leading equal sign in the value definition!):

'11/11/2015', '25/12/2015', '01/01/2016', '28/03/2016'

Then use it like

NetWorkDays(MakeDate(only(Year),only(Month),1),MonthEnd(MakeDate(only(Year),only(Month),1)),$(vHolidays))

Re: Exclude days in an expression using a field

kaushiknsolanki — Tue, 15 Mar 2016 10:14:56 GMT

Hi,

Hope below link will help.

Find Net Working Days

Regards,

Kaushik Solanki

Re: Exclude days in an expression using a field

Tue, 15 Mar 2016 11:35:05 GMT

Hello Swuehl,

Is this the way to add the variable, 'cus it gives me red lines already..

Edit: IT WORKS!! Thanks a bunch Swuehl, this is exactly what I needed!

Re: Exclude days in an expression using a field

Gysbert_Wassenaar — Tue, 15 Mar 2016 11:40:47 GMT

Yes, that's fine. The expression editor syntax checker tries to evaluate it as an expression. Well, it's not a valid expression. But it is simply a comma separated list of values which is what it should be.