How to find the exact number of Saturdays and Sundays in between two date fields

[pranaview]

Hey Guys,

Please read below to know what exactly I am looking for

Requirement :  I have a Delivery Date field in my data and a Lead Time field which is nothing but number of days like 7, 10 etc. I want to determine the Deadline Date from these two variables with the following formula 

Deadline Date = Delivery Date - Lead Time

Now what I need to check here is how many Saturdays and Sundays are occurring in the Lead Time duration. For example, if Lead Time is 10 and when I check these 10 days in the calendar and  see that there are 2 Sat and 2 Sun in this period then My  new Lead time would be 10 + 4 = 14.

What I though to do is get the deadline date from the above formula with the Lead Time being 10 days and then I will try to find out how many sat/sun are there in between these two dates then I will just add the result to my original Lead Time and again subtract it from delivery date to get the final Deadline Date.But I don't really know how to execute this as I'm pretty new to Qlik world or even if this is the right approach.

So any help or a suggestion will be immensely appreciated as I have to come up with the solution by tomorrow.

Thanks,

Pranav

[tresesco]

In qlik there is a function called networkdays() that gives number of working days (days excluding Sat, Sun). Hence, if you subtract this output from the day difference between the dates, you get no of Sun, Sat days. So it would be something like:

NoOfSatSunDays = EndDate-StartDate - Networkdays(StartDate,EndDate) 

[sunny_talwar]

I think what you really need to use here is firstworkdate()/lastworkdate() functions which will automatically take weekends into consideration when calculating which ever date field (Deadline Date or Delivery Date) you are trying to calculate

firstworkdate

lastworkdate

[pranaview]

Thanks for the suggestion Tresesco.

I actually tried this solution but it does not seem to give correct result.

For ex: If I take Start Date as "20/11/2018" and End Date as "28/11/2018" then I get 1 as the result but I should be getting 2.

Do you think I can modify this expression to get the desired output?

[sunny_talwar]

I guess you need to add 1 to the calculation to get two

[sunny_talwar · Accepted solution]

Something like this

=MakeDate(2018, 11, 28) - MakeDate(2018, 11, 20) - NetWorkDays(MakeDate(2018, 11, 20), MakeDate(2018, 11, 28)) + 1

[pranaview]

Hey Sunny, thanks a lot for the suggestion, I am testing both of them and I think little bit of adjustment will get the job done.