topic Re: Exclude Weekends from Calculation in QlikView

Exclude Weekends from Calculation

JustinRamsey — Fri, 04 Feb 2022 20:28:11 GMT

I am in need of assistance writing a formula to give me the time between two time stamps excluding weekends. See image for the data. I want the end result to be as in the chart with days and the decimal not strictly the number of days as it is below just excluding the weekends. Networkdays wont work with the set analysis and using time. Formulas for columns

Picking =SUM({<%KEY_ARBPL={2001}>}Date_Time)

RGA= SUM({<%KEY_ARBPL={2000}>}Date_Time)

Difference = Column(2)-Column(1)

Re: Exclude Weekends from Calculation

Thiago_Justen_ — Sun, 06 Feb 2022 15:36:33 GMT

Hey @JustinRamsey

You could try this:

Difference = NetWorkDays(Column(1),Column(2))

Hope it helps you.

Regards

Re: Exclude Weekends from Calculation

anat — Sun, 06 Feb 2022 22:06:40 GMT

considering 2 variables:

v1 = 2020-08-21 20:00:00 (older timestamp)

v2 = 2020-08-24 21:00:00 (newer timestamp)

This expression will use the NetWorkDays function to get the working days (excluding weekends), and the second part will calculate the difference on the time part:

=interval((NetWorkDays('$(v1)', '$(v2)') - 1) + (frac('$(v2)') - frac('$(v1)')),'hh')

Re: Exclude Weekends from Calculation

JustinRamsey — Mon, 07 Feb 2022 12:05:16 GMT

This formula works to get the total days but only gives the whole number of days. Would like to see the number of days and a decimal for the amount of hours.

Re: Exclude Weekends from Calculation

JustinRamsey — Mon, 07 Feb 2022 12:17:11 GMT

I cannot get this to give results. I am not sure if I am defining the variables wrong or not. Also attempted to use just the columns instead of variables to no avail. Variable definitions below. I will admit I have not used variables much so I may be going about that portion incorrectly.

Re: Exclude Weekends from Calculation

JustinRamsey — Mon, 07 Feb 2022 13:37:19 GMT

Actually discussed with the end user and they agreed straight days only would be sufficient for the needs. Thanks!

Re: Exclude Weekends from Calculation

Krishn1 — Wed, 18 Sep 2024 10:31:27 GMT

I have used the same formula, However, it is giving incorrect results when the start date and end date are falling on weekends. Any suggestions ??