Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gsmajdor
Contributor

Add Working Days to a date

Hi,

Is there any function to add working days?

The function should do the same as Excel function Workday(date, workingDaysToAdd)

I tried lastworkdate() function, but the function doesn't return the same results as Excel Workday()

   

qlik: lastworkdate(date, 5)

excel: workday(date, 5)

date       qlik            excel
25-Mar-1731-Mar-1731-Mar-17
26-Mar-1731-Mar-1731-Mar-17
27-Mar-1731-Mar-173-Apr-17
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Add Working Days to a date

Seems like Qlik is including 27th, where as Excel might be starting the count from 28th... it's just the matter of what you want... may be all you want is this

LastWorkDay(date+1, 5)

7 Replies
prat1507
Valued Contributor

Re: Add Working Days to a date

Maybe this

If(Weekday(date)=Sun or Weekday(date)=Sat, lastworkdate(date, 5), lastworkdate(date, 5+1))


Regards

Pratyush

techvarun
Valued Contributor II

Re: Add Working Days to a date

=WORKDAY(Date,5) - Will not consider the date and show fifth working day from the Date

=lastworkdate (Date, 5)   = Will check the date is Holiday or not first if yes it will consider that also and show the fifth date

MVP
MVP

Re: Add Working Days to a date

Seems like Qlik is including 27th, where as Excel might be starting the count from 28th... it's just the matter of what you want... may be all you want is this

LastWorkDay(date+1, 5)

gsmajdor
Contributor

Re: Add Working Days to a date

Thanks, it works.

I realized it works only for positive numbers.

Any solution for negative numbers? I mean something like lastworkday(date, -5)

gsmajdor
Contributor

Re: Add Working Days to a date

Thanks, btw it seems you missed single quotes signs.

if(Weekday(date)='Sun' or Weekday(date)='Sat', lastworkdate(date, 5), lastworkdate(date, 5+1))


btw, Instead of Weekday(date)='Sun' it's more safely to use Weekday(date)=6


MVP
MVP

Re: Add Working Days to a date

Check if FirstWorkDay() works for that...

FirstWorkDay(date, 5)

gsmajdor
Contributor

Re: Add Working Days to a date

It seems that for negative numbers we have to use

FirstWorkDay(date-1, 5)


That's really bad we have to use two different functions for (basically) the same.

Excel is better again.