Date Help

[mahitham]

Hi Experts,

Can any one please help me on below requirement. I have a [Actual Date] field in Time stamp format.

I have converted Time stamp to normal format by Date(floor([Actual Date])) as [Actual Date]

Requirement:

Now from this [Actual Date] I need to derive a New field called [Estimate Date] like below i.e.,

Value of [Actual Date] field + 35 business days

A “business day” is defined as any Monday, Tuesday, Wednesday, Thursday or Friday that is not a bank holiday date in England in the relevant year.

Please help me on this.

Thanks in advance.

[VishalWaghole]

Hi,

https://community.qlik.com/t5/QlikView-App-Development/Rolling-5-days-working-days/td-p/980823

Hope this post will solve your problem.

Thanks,
Vishal Waghole

[mahitham]

Hi @VishalWaghole 

I am working on Qliksense. I don't have the Qlikview license. Could you please help me with the attached Business Days qvd based on Actual Date field need to add 35 business days.

I have null values also in Actual Date for those need to skip business days adding.

[Channa · Accepted solution]

lastworkdate ('03/21/2019', 35)

this will bring u date excluding weekends try

[mahitham]

HI @Channa 

Thanks a lot for your reply. I have tried your solution. I am getting 2 dates as correct which are highlighted in green color and remaining red color highlighted dates are not matching with the output.

Could you please help me on this.

Please find the attached sample app.

[Channa]

Can you share some sample data also

[mahitham]

Hi @Channa 

Thanks for your reply.

Please find the below attached QVD which contains the Actual Date field.

[Channa]

Bro your expected results is wrong

for jan 22nd 2018 if you add 35 business days you dont get Feb 9th 2018 it is only adding  15 business days

please review

https://www.timeanddate.com/date/workdays.html?d1=22&m1=1&y1=2018&d2=9&m2=3&y2=2018&ti=on&

use the above  to calculate 

[mahitham]

Hi @Channa 

Thanks a lot for  your reply.

I have checked the Second Row date 02/04/2018 for this I am getting 18/05/2018 result In Qlik.

To validate I have selected these date in the below Business Days Calculator for United Kingdom- England.

But getting the result as 33 days instead of 35 days after excluding Saturdays ,Sundays and bank holidays. But it should be 35 days result.

[Channa]

bro if you want to exclude public holidys

lastworkdate ('03/21/2019', 35,Vdate)

pass all your public holidays into variable..