Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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-17 | 31-Mar-17 | 31-Mar-17 |
26-Mar-17 | 31-Mar-17 | 31-Mar-17 |
27-Mar-17 | 31-Mar-17 | 3-Apr-17 |
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)
Maybe this
If(Weekday(date)=Sun or Weekday(date)=Sat, lastworkdate(date, 5), lastworkdate(date, 5+1))
Regards
Pratyush
=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
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)
Thanks, it works.
I realized it works only for positive numbers.
Any solution for negative numbers? I mean something like lastworkday(date, -5)
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
Check if FirstWorkDay() works for that...
FirstWorkDay(date, 5)
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.